Solved

A Trigger For All Deletes In A Schema

Posted on 2004-03-25
9
508 Views
Last Modified: 2010-08-05
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
Comment
Question by:mmemon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 10677908
Normal triggers cannot be based on a schema or a database.
So you can trigger only schema or database specific events.  
0
 
LVL 8

Expert Comment

by:baonguyen1
ID: 10678607
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
 

Author Comment

by:mmemon
ID: 10678769
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

by:
baonguyen1 earned 125 total points
ID: 10679006
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
 

Expert Comment

by:mickyfinn
ID: 10681955
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
 
LVL 2

Expert Comment

by:aabbas
ID: 10686525
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
 
LVL 3

Expert Comment

by:rajnadimpalli
ID: 10697247
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question