Skip to Main Content
  • Questions
  • Before Delete trigger and prevent deletion. Track the entry in EVENT_DETAILS table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Himabindu.

Asked: November 15, 2016 - 7:05 pm UTC

Last updated: November 18, 2016 - 4:14 am UTC

Version: 11.1.0.6.0

Viewed 10K+ times! This question is

You Asked

Good Afternoon Tom..!!
My requirement is when user trying to delete a record from T1 table. If an attempt to delete an entry from T1 is captured, create an entry in the EVENT_DETAILS and the record will not be deleted from T1. When rollback, EVENT_DETAILS row will not be rollback.

I've tried in 3 ways.
1. Trigger code and Put Insert statement(COMMIT) in a procedure as pragma autonomous transaction.
Issue: pls-00127: Pragma AUTOMOMOUS_TRANSACTION is not a supported pragma

2. Tigger code includes Insert statement and Raise exception . Handling it in Exception block. Insert successful and it is deleting data.
3. The below code, insert is successful, not preventing deletion.

Please suggest the best feasible way and let me know If missed anything. Thank you.
FYI- I just put DELETE trigger code only. Later I will add INSERT/UPDATE CODE ON T1.

Also, I will need to repeat the same code for other two tables. Is there any way to reuse the same code for multiple tables?

CREATE OR REPLACE TRIGGER BEFORE_DML_DBLOGIN
BEFORE DELETE OR INSERT OR DELETE ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_LOGIN_USER VARCHAR2 (30);
V_NODE_IP VARCHAR2 (30);
V_OSUSER VARCHAR2 (30);
V_PROGRAM VARCHAR2 (120);
_LOGIN_USER NUMBER := 0;
V_ACTIVE VARCHAR2 (10);

BEGIN
SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS'),
SYS_CONTEXT ('USERENV', 'OS_USER')
INTO V_NODE_IP, V_OSUSER
FROM DUAL;

SELECT UPPER (PROGRAM)
INTO V_PROGRAM
FROM V$SESSION
WHERE AUDSID = SYS_CONTEXT ('USERENV', 'SESSIONID') AND ROWNUM = 1;

IF DELETING THEN
INSERT
INTO EVENT_DETAILS(IP_ADDRESS,DB_LOGIN,OS_USER,EXECUTABLE,CREATED)
VALUES (V_NODE_IP,SYS.LOGIN_USER,V_OSUSER,V_PROGRAM, SYSDATE)
);
RAISE_APPLICATION_ERROR (-20002, 'T1 table data can not be deleted', TRUE);
END IF;

EXCEPTION
WHEN OTHERS THEN
INSERT
INTO EVENT_DETAILS(IP_ADDRESS,DB_LOGIN,OS_USER,EXECUTABLE,CREATED)
VALUES (V_NODE_IP,SYS.LOGIN_USER,V_OSUSER,V_PROGRAM, SYSDATE)
);
RAISE_APPLICATION_ERROR (-20002, 'T1 table data can not be deleted', TRUE);
END BEFORE_DML_DBLOGIN;
/


with LiveSQL Test Case:

and Connor said...

There is no problem with autonomous transactions in the trigger, eg

SQL> create table t ( x int );

Table created.

SQL> insert into t values (1);

1 row created.

SQL>
SQL> create table err ( d date, x int );

Table created.

SQL>
SQL> create or replace
  2  trigger TRG
  3  before delete on T
  4  for each row
  5  declare
  6    pragma autonomous_transaction;
  7  begin
  8    insert into err values (sysdate,:old.x);
  9    commit;
 10    raise_application_error(-20000,'No you cannot delete');
 11  end;
 12  /

Trigger created.

SQL>
SQL> delete from t;
delete from t
            *
ERROR at line 1:
ORA-20000: No you cannot delete
ORA-06512: at "MCDONAC.TRG", line 6
ORA-04088: error during execution of trigger 'MCDONAC.TRG'


SQL> select * from err;

D                  X
--------- ----------
16-NOV-16          1

1 row selected.


But note that triggers can fire more than once for single execution, even for a single row, due to the way we handle multi-user operations on a table. So whilst you can track activity, you can't assume that just because someone appears (say) twice in the your error table, that they attempted the operation 2 times. They may have attempted it just once.


If you needed to reuse the code elsewhere, you could just make the code into a procedure, eg

SQL> create or replace
  2  procedure catch_del_attempt(p_x int) is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into err values (sysdate,p_x);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL>
SQL> create or replace
  2  trigger TRG
  3  before delete on T
  4  for each row
  5  begin
  6    catch_del_attempt(:old.x);
  7    raise_application_error(-20000,'No you cannot delete');
  8  end;
  9  /

Trigger created.


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

apply privileges, add api layer

Rob van den Berg, November 16, 2016 - 8:57 am UTC

I do presume your users connect using their own schema.
If deleting is disallowed anyways, consider not granting the delete privilege. Why log actions that should have been prevented anyhow.
One step further would be to only allow access to your tables through an API. In your case, the 'delete' interface would be missing. But if you just have to obey the requirements, it would simply insert the log message.
Connor McDonald
November 17, 2016 - 2:33 am UTC

nice input

Working as expected with some errors those are warnings or not ?

Himabindu Baddila, November 16, 2016 - 3:37 pm UTC

Thank you very much,Tom for quick response.
I've written the below code and executed. As expected, it is not deleting data from DBLOGIN table and capturing the entry in
EVENT_DETAILS table.
My question is, I got the below error at INSERT statement; I verified it nothing goes wrong. Is it an ignorable error?

Trigger Code:
SQL> CREATE OR REPLACE TRIGGER trg_1
2 before delete ON DBLOGIN
3 declare
4 pragma autonomous_transaction;
5 v_ip varchar2(100);
6 v_os varchar2(100);
7 v_user varchar2(100);
8 v_pgm varchar2(100);
9 begin
10 SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS'), SYS_CONTE
XT ('USERENV', 'OS_USER')
11 INTO v_ip, v_os FROM DUAL;
12
13 SELECT UPPER (PROGRAM) INTO v_pgm
14 FROM GV$SESSION
15 WHERE AUDSID = SYS_CONTEXT ('USERENV', 'SESSIONID') AND ROWNUM = 1
;
16
17 IF (SYS_CONTEXT ('USERENV', 'SESSION_USER') NOT IN ('ADT'))
18 THEN
19 IF DELETING THEN
20 INSERT INTO EVENT_DETAILS (IDS,PROGRAMS,EVENT,OBJECTS,IP
_ADDRESS,DB_LOGIN,OS_USER,EXECUTABLE,CREATED) VALUES(DB_ADMIN.EVENT_SEQ.NEXTVAL,
'NY E-ZPass','Deletion Attempt','DBLOGIN_USER_INFO',V_IP,SYS.LOGIN_USER,V_OS,V_P
GM,SYSDATE);
21 COMMIT;
22 RAISE_APPLICATION_ERROR (-20002, 'DBLOGIN table data can
not be deleted');
23 END IF;
24 END IF;
25
26 END;
27 /

DELETE FROM DBLOGIN_USER_INFO
*
ERROR at line 1:
ORA-20002: DBLOGIN table data can not be deleted
ORA-06512: at "TRG_1", line 20
ORA-04088: error during execution of trigger 'TRG_1'


SQL>
Connor McDonald
November 17, 2016 - 2:31 am UTC

The plsql code starts at line 3, so the 20th line is actually the raise

delete in Oracle before trigger

Hima, November 17, 2016 - 2:32 pm UTC

Many thanks Tom and Connor McDonald. thanks for your time.

I believe, So the exception is at raise throws a user defined message as expected and this is not something else and we can ignore it.

As per the requirement, the schema has all DML operations If anyone is trying to do Delete, Insert and Update we need to design the trigger code.

Thank you.
Connor McDonald
November 18, 2016 - 4:14 am UTC

yes

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library