• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

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
0
mmemon
Asked:
mmemon
1 Solution
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
baonguyen1Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now