A Trigger For All Deletes In A Schema

Hello,

I want to create a trigger that captures user,object,event(which would be a delete) and most importantly ip_address when a user performs a delete on any table within a schema.

I created a trigger to execute on the database(see below) when a DDL event takes place:

CREATE TRIGGER DDLTRIGGER AFTER DDL ON DATABASE
DECLARE
ip_addr    VARCHAR2(30);

BEGIN

SELECT sys_context ('USERENV', 'IP_ADDRESS')
  INTO ip_addr
  FROM dual;

INSERT INTO DDLEvents
 (UserName, DDLDate, DDLObjType, DDLObjOwner, DDLObjName, DDLEvent,IP_ADDR)
 VALUES
 (ora_login_user, sysdate, ora_dict_obj_type,ora_dict_obj_owner, ora_dict_obj_name, ora_sysevent,ip_addr);
END;

Is there a way to create a trigger that will insert basically the same information above into a table when a delete occurs on any table within a schema?

Thanks
Michele
mmemonAsked:
Who is Participating?
 
baonguyen1Connect With a Mentor Commented:
Yes, using user event trigger you can only trigger on the statements: CREATE, ALTER, DROP, ANALYZE, RENAME, TRUNCATE on a object other than INSERT, UPDATE, DELETE ... For example, you can log every object name, type that the user A drop:

create trigger drop_objs
before drop on a.schema
begin
insert into table values(ora_dict_obj_name, ora_dict_obj_type,
ora_client_ip_address, ....);
end;

HTH


0
 
schwertnerCommented:
Normal triggers cannot be based on a schema or a database.
So you can trigger only schema or database specific events.  
0
 
baonguyen1Commented:
Add more to schwertner.

You have five type of triggers:

1. Statement triggers: on particular statement or statement that act on a table, view
2. Row triggers: fire on each row affected
3. Instead of triggers: execute in place of actual issuing statenment
4.System Event Trigger: fire when db startup, shutdown, server errors.
5. User Event triggers:user logon/off, DDL, DML actions.

So you can not create a DATABASE trigger as you specified but you can create a trigger to fire when a specific schema do some actions on objects ...

Hope this helps
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
mmemonAuthor Commented:
Thank you for your responses.

I was trying to avoid writting a trigger for each table. I would like to store some information when a delete happens on any table in the schema. I guess that is not possible.
0
 
mickyfinnCommented:
SELECT
'create trigger catch_del_'||table_name||' after delete on'||owner||'.'||table_name||'
begin
insert into history_tables values (ora_dict_obj_name, ora_dict_obj_type,
ora_client_ip_address, ....); end;'
FROM ALL_TABLES

cut and paste output from above back into SQLPlus.  You'll need to edit the insert to match whatever your log table looks like.

Micky
0
 
aabbasCommented:
Yes, probably this will reduce stuff to type. But the above script will create triggers for all the tables that exist within the database (i.e. for all schemas).

You will need to change it as follows, so catch just a specific schema.

select
'create trigger catch_del_' || table_name || ' after delete on ' || table_name || '
begin
insert into Del_Events
 (UserName, Del_Date, Table_Name, IP_ADDR)
 values
 (user, sysdate, ora_dict_obj_name, sys_context (''USERENV'', ''IP_ADDRESS''));
end;'
from user_tables;

Hope it will help.

Best of luck.
0
 
rajnadimpalliCommented:
Are we forgetting that Oracle Audit trails can able to capture IP_address/USER_HOST/USER Terminal information ...along with user actions on objects...This is must better compared to triggers with very low foot print too...

See below.....

SQL> show parameter audit

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations                 boolean     FALSE
audit_trail                          string      DB <---- Database Audit Trails..
transaction_auditing                 boolean     TRUE
SQL> select count(*) from sys.aud$;

  COUNT(*)
----------
         0

SQL> select * from dba_audit_trail;

no rows selected

SQL> AUDIT DELETE TABLE BY SCOTT;

Audit succeeded.

SQL>
================

SQL> delete from  employees where rownum < 2;

1 row deleted.

SQL> commit;

Commit complete.

SQL> show user
USER is "SCOTT"
SQL>
================


SQL>@hsel
enter statement : select * from sys.aud$

SESSIONID                     : 140
ENTRYID                         : 5
STATEMENT                     : 16
TIMESTAMP#                    : 27-mar-2004 13:14:50
USERID                        : SCOTT             <----- Use
USERHOST                      :     <------------------ Some cases this will be populated IP address
TERMINAL                      : RAJ-R29YPPRS7WU   <----- User Terminal Name/User PC name/IP address
ACTION#                       : 103    <--- Event (103 = Delete)
RETURNCODE                    : 0
OBJ$CREATOR                   : SCOTT  
OBJ$NAME                      : EMPLOYEES         <---- Table name
AUTH$PRIVILEGES               :
AUTH$GRANTEE                  :
NEW$OWNER                     :
NEW$NAME                      :
SES$ACTIONS                   : ---S------------  <--- Event
SES$TID                       : 5924
LOGOFF$LREAD                  :
LOGOFF$PREAD                  :
LOGOFF$LWRITE                 :
LOGOFF$DEAD                   :
LOGOFF$TIME                   :
COMMENT$TEXT                  :
CLIENTID                      :
SPARE1                        : RAJ-R29YPPRS7WU\Administrator
SPARE2                        :
OBJ$LABEL                     :
SES$LABEL                     :
PRIV$USED                     :
SESSIONCPU                    :
-----------------

SQL> desc sys.AUD$
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 SESSIONID                     NOT NULL NUMBER
 ENTRYID                       NOT NULL NUMBER
 STATEMENT                     NOT NULL NUMBER
 TIMESTAMP#                    NOT NULL DATE
 USERID                                 VARCHAR2(30)
 USERHOST                               VARCHAR2(128)
 TERMINAL                               VARCHAR2(255)
 ACTION#                       NOT NULL NUMBER
 RETURNCODE                    NOT NULL NUMBER
 OBJ$CREATOR                            VARCHAR2(30)
 OBJ$NAME                               VARCHAR2(128)
 AUTH$PRIVILEGES                        VARCHAR2(16)
 AUTH$GRANTEE                           VARCHAR2(30)
 NEW$OWNER                              VARCHAR2(30)
 NEW$NAME                               VARCHAR2(128)
 SES$ACTIONS                            VARCHAR2(19)
 SES$TID                                NUMBER
 LOGOFF$LREAD                           NUMBER
 LOGOFF$PREAD                           NUMBER
 LOGOFF$LWRITE                          NUMBER
 LOGOFF$DEAD                            NUMBER
 LOGOFF$TIME                            DATE
 COMMENT$TEXT                           VARCHAR2(4000)
 CLIENTID                               VARCHAR2(64)
 SPARE1                                 VARCHAR2(255)
 SPARE2                                 NUMBER
 OBJ$LABEL                              RAW(255)
 SES$LABEL                              RAW(255)
 PRIV$USED                              NUMBER
 SESSIONCPU                             NUMBER

SQL>

-R

0
All Courses

From novice to tech pro — start learning today.