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