Skip to Main Content
  • Questions

  • DML operations inside Compound triggers

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Priya.

Asked: November 20, 2016 - 6:36 pm UTC

Last updated: December 02, 2016 - 2:37 am UTC

Version: 11.1.0.6.0

Viewed 1000+ times

You Asked

As suggested I use COMPOUND TRIGGERS, Please find requirements & the below code below.

1. IF DELETING, any record created in USER_INFO table may not be deleted.
Attempting to delete a record from this table is captured, create an entry in AUDIT table.
2. IF INSERTING, required fields are ID,LOGIN_USER

• Before inserting into a table, validate there is not an existing record with the same LOGIN_USER
and the record status is True.

• If this validation fails, return error code and message indicating failure.

• When a record is inserted in to a table, a post-event trigger will fire updating the following
fields: ID with sequence, Created Current Date/Time and Action “Record insertion”
3. IF UPDATING, Required Fields are ID,LOGIN_USER

• When a record is updated in a table, a post-event trigger will fire does the following:

o Insert record into effected table copying current values of the fields.

o The fields ID,STATUS,LOGIN_USER are populated or overridden with given values

• A pre-event trigger will fire and prevent an inactive record from being updated.


The issues are:
1. During DELETE, Im getting the RAISE error as expected but not inserting entry in audit table.
2. During INSERT, fire_on_insert FALSE is carrying instead TRUE.
3. During UPDATE, when I do update it is indefinitely running the qquery.

Please suggest the final code as Im running out of time. This is first time Im using compound triggers.

CREATE OR REPLACE TRIGGER CMP_TRIG
FOR DELETE OR INSERT OR UPDATE OF ID,NUM,LOGIN_USER ON T1
COMPOUND TRIGGER

TYPE t_DBLOGIN IS TABLE OF T %ROWTYPE;
v_DBLOGON t_DBLOGIN := t_DBLOGIN();
I NUMBER :=0;
V_LOGIN_USER NUMBER;
TYPE t_audit IS TABLE OF AUDIT_TAB %ROWTYPE;
v_audit t_audit := t_audit();
j NUMBER :=0;

BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('In before statement');
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
v_audit.extend(1000);
v_DBLOGON.extend(1000);
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE('BEFORE DELETE');
j := j+1;
v_audit(j).ID := AUDIT_SEQ.NEXTVAL;
v_audit(j).ACTION := 'RECORD DELETION';
v_audit(j).OBJECTS := 'T1';
v_audit(j).IP_ADDR := SYS_CONTEXT ('USERENV', 'IP_ADDRESS');
v_audit(j).DB_LOGIN := SYS.LOGIN_USER;
v_audit(j).OS_USER := SYS_CONTEXT ('USERENV', 'OS_USER');
v_audit(j).CREATED := SYSDATE;
END IF; --Close IF for DELETING

IF INSERTING THEN
IF INSERT_FLAG.fire_on_insert = 'TRUE' THEN
SELECT COUNT (1) INTO V_LOGIN_USER FROM T1
WHERE LOGIN_USER = :NEW.LOGIN_USER AND STAUTS='TRUE';

IF ( V_LOGIN_USER >=1 ) THEN
RAISE_APPLICATION_ERROR (-20002, 'DATABASE LOGIN USER ALREADY EXISTS', TRUE);
END IF;
I :=I+1;
v_DBLOGON(I).ID := DB_SEQ.NEXTVAL;
v_DBLOGON(I).CREATED :=SYSDATE;
v_DBLOGON(I).CREATED_BY := USER;
v_DBLOGON(I).Event := 'RECORD INSERT';
END IF;
END IF; --CLOSE IF FOR INSERTING

IF UPDATING THEN
IF :OLD.STATUS = 'INACTIVE' THEN
RAISE_APPLICATION_ERROR (-20002, 'INACTIVE RECORDS CAN NOT BE UPDATED', TRUE);
END IF;
I := I+1;
v_DBLOGON(I).ID := DB_SEQ.NEXTVAL;
v_DBLOGON(I).STATUS := 'FALSE';
v_DBLOGON(I).LOGIN_USER := :OLD.LOGIN_USER;
END IF;

END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
IF DELETING THEN
forall J in 1..v_audit.count()
insert into AUDIT_TAB values v_audit(J);
RAISE_APPLICATION_ERROR (-20002, 'DBLOGIN_USER_INFO table data can not be deleted', TRUE);
END IF;

IF INSERTING THEN
Forall I in 1..v_DBLOGON.count()
insert into T1 values v_DBLOGON(I);
END IF;

IF UPDATING THEN
INSERT_FLAG.fire_on_insert := 'FALSE';
forall I in 1..v_DBLOGON.count()
insert into T1 values v_DBLOGON(I);
INSERT_FLAG.fire_on_insert := 'TRUE';
END IF;
END AFTER STATEMENT;

END CMP_TRIG;
/

and Connor said...

Nice work - you've got pretty close there. Here's my code using cut-down versions of the table, but the requirement is the same, ie

insert => populate other columns (created etc)
update => keep a copy of the previous row
delete => not allowed, audit kept of the attempt

I added a proc to the package used for the insert variable to handle the autonmous stuff.


SQL> drop table t1 purge;

Table dropped.

SQL> create table t1 ( x int ,y int, created date, created_by varchar2(10));

Table created.

SQL>
SQL> drop table audit_tab purge;

Table dropped.

SQL> create table audit_tab ( action varchar2(10), who varchar2(20), dte date, x int ,y int);

Table created.

SQL>
SQL>
SQL> create or replace
  2  package audit_pkg is
  3    type t_dblogin is table of t1%rowtype index by pls_integer;
  4    type t_audit is table of audit_tab%rowtype index by pls_integer;
  5
  6    fire_on_insert boolean := true;
  7
  8    procedure delete_separate_transaction(p_audit t_audit);
  9  end;
 10  /

Package created.

SQL>
SQL> create or replace
  2  package body audit_pkg is
  3    procedure delete_separate_transaction(p_audit t_audit) is
  4      pragma autonomous_transaction;
  5    begin
  6
  7      forall j in 1..p_audit.count()
  8        insert into audit_tab values p_audit(j);
  9      commit;
 10    end;
 11  end;
 12  /

Package body created.

SQL>
SQL> create or replace trigger cmp_trig
  2  for delete or insert or update  on t1
  3  compound trigger
  4
  5    v_dblogon audit_pkg.t_dblogin;
  6    v_audit audit_pkg.t_audit;
  7
  8  before each row is
  9  begin
 10    if deleting then
 11      v_audit(v_audit.count+1).action := 'delete';
 12      v_audit(v_audit.count).who := user;
 13      v_audit(v_audit.count).dte := sysdate;
 14      v_audit(v_audit.count).x := :old.x;
 15      v_audit(v_audit.count).y := :old.y;
 16    end if; --close if for deleting
 17
 18    if inserting then
 19      if audit_pkg.fire_on_insert  then
 20        :new.created :=sysdate;
 21        :new.created_by := user;
 22      end if;
 23    end if; --close if for inserting
 24
 25    if updating then
 26      v_dblogon(v_dblogon.count+1).x := :old.x;
 27      v_dblogon(v_dblogon.count).y := :old.x;
 28      v_dblogon(v_dblogon.count).created := :old.created;
 29      v_dblogon(v_dblogon.count).created_by := :old.created_by;
 30    end if;
 31
 32  end before each row;
 33
 34
 35  after statement is
 36  begin
 37    if deleting then
 38       audit_pkg.delete_separate_transaction(v_audit);
 39       raise_application_error (-20002, 'dblogin_user_info table data can not be deleted', true);
 40    end if;
 41
 42    if updating then
 43      audit_pkg.fire_on_insert := false;
 44        forall i in 1..v_dblogon.count()
 45          insert into t1 values v_dblogon(i);
 46      audit_pkg.fire_on_insert := true;
 47    end if;
 48  end after statement;
 49
 50  end cmp_trig;
 51
 52  /

Trigger created.

SQL> sho err
No errors.
SQL>
SQL>
SQL> insert into t1  ( x , y ) values (1,2);

1 row created.

SQL> select * from t1;

         X          Y CREATED   CREATED_BY
---------- ---------- --------- ----------
         1          2 21-NOV-16 MCDONAC

1 row selected.

SQL> select * from audit_tab;

no rows selected

SQL>
SQL> insert into t1  ( x , y ) values (3,4);

1 row created.

SQL> select * from t1;

         X          Y CREATED   CREATED_BY
---------- ---------- --------- ----------
         1          2 21-NOV-16 MCDONAC
         3          4 21-NOV-16 MCDONAC

2 rows selected.

SQL> select * from audit_tab;

no rows selected

SQL>
SQL> update t1
  2  set y = 5
  3  where x = 1;

1 row updated.

SQL> select * from t1;

         X          Y CREATED   CREATED_BY
---------- ---------- --------- ----------
         1          5 21-NOV-16 MCDONAC
         3          4 21-NOV-16 MCDONAC
         1          1 21-NOV-16 MCDONAC

3 rows selected.

SQL> select * from audit_tab;

no rows selected

SQL>
SQL> delete from t1
  2  where x = 3;
delete from t1
            *
ERROR at line 1:
ORA-20002: dblogin_user_info table data can not be deleted
ORA-06512: at "MCDONAC.CMP_TRIG", line 37
ORA-04088: error during execution of trigger 'MCDONAC.CMP_TRIG'


SQL> select * from t1;

         X          Y CREATED   CREATED_BY
---------- ---------- --------- ----------
         1          5 21-NOV-16 MCDONAC
         3          4 21-NOV-16 MCDONAC
         1          1 21-NOV-16 MCDONAC

3 rows selected.

SQL> select * from audit_tab;

ACTION     WHO                  DTE                X          Y
---------- -------------------- --------- ---------- ----------
delete     MCDONAC              21-NOV-16          3          4

1 row selected.

SQL>
SQL>



Exercise for you - make sure that you reset the flag in the event of an error, so that the insert-flag is never left *erroneously* on.

Rating

  (6 ratings)

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

Comments

COMPOUND TRIGGER RESOLUTION

Priya, November 21, 2016 - 8:28 pm UTC

Awsome solution. It works perfect with small change.

I got below error..When I call,procedure delete_XXXX in "AFTER STATEMENT"
ORA-04084: cannot change NEW values for this trigger type
after statement is
36 begin
37 if deleting then
38 audit_pkg.delete_separate_transaction(v_audit);
39 raise_application_error (-20002, 'dblogin_user_info table data can not be deleted', true);
40 end if;

Later, I put the above code in "BEFORE EACH ROW" then it works..
before each row is
9 begin
10 if deleting then
11 v_audit(v_audit.count+1).action := 'delete';
12 v_audit(v_audit.count).who := user;
13 v_audit(v_audit.count).dte := sysdate;
14 v_audit(v_audit.count).x := :old.x;
15 v_audit(v_audit.count).y := :old.y;

audit_pkg.delete_separate_transaction(v_audit);
39 raise_application_error (-20002, 'dblogin_user_info table data can not be deleted', true);
16 end if; --close if for deleting
17

As expected, I got this message plus capturing entry in audit table..Plz suggest can I follow the same ?

ORA-20002: dblogin_user_info table data can not be deleted
ORA-06512: at "DB_ADMIN.CMP_TRIG_1", line 24
ORA-04088: error during execution of trigger 'DB_ADMIN.CMP_TRIG_1'
Connor McDonald
November 22, 2016 - 1:12 am UTC

We need to see your entire code, because

after statement is
36 begin
37 if deleting then
38 audit_pkg.delete_separate_transaction(v_audit);
39 raise_application_error (-20002, 'dblogin_user_info table data can not be deleted', true);
40 end if;

does not look to be changing any :new values to me

Follow up of Compound Trigger

Priya, November 22, 2016 - 4:54 pm UTC


I executed the same code as below. Experienced issues..

SQL> CREATE OR REPLACE package DB_ADMIN.TRI_pkg is
2 type t_dblogin is table of t1%rowtype index by pls_integer;
3 type t_audit is table of audit_tab%rowtype index by pls_integer;
4
5 fire_on_insert boolean := true;
6
7 procedure delete_separate_transaction(p_audit t_audit);
8 end;
9 /

Package created.

SQL> CREATE OR REPLACE package body DB_ADMIN.TRI_pkg is
2 procedure delete_separate_transaction(p_audit t_audit) is
3 pragma autonomous_transaction;
4 begin
5
6 forall j in 1..p_audit.count()
7 insert into audit_tab values p_audit(j);
8 commit;
9 end;
10 end;
11 /

Package body created.

SQL> drop table t1 purge;


SQL> /

Table dropped.

SQL> create table t1 ( x int ,y int, created date, created_by varchar2(10));

Table created.


SQL> drop table audit_tab purge;

Table dropped.

SQL> create table audit_tab ( action varchar2(10), who varchar2(20), dte date, x
int ,y int);

Table created.

SQL>


SQL> create or replace trigger trig_AUDIT
2 for delete or insert or update on t1
3 compound trigger
4
5 v_dblogon TRI_pkg.t_dblogin;
6 v_audit TRI_pkg.t_audit;
7
8 before each row is
9 begin
10 if deleting then
11 v_audit(v_audit.count+1).action := 'delete';
12 v_audit(v_audit.count).who := user;
13 v_audit(v_audit.count).dte := sysdate;
14 v_audit(v_audit.count).x := :old.x;
15 v_audit(v_audit.count).y := :old.y;
16 end if; --close if for deleting
17
18 if inserting then
19 if TRI_pkg.fire_on_insert then
20 :new.created :=sysdate;
21 :new.created_by := user;
22 end if;
23 end if; --close if for inserting
24
25 if updating then
26 v_dblogon(v_dblogon.count+1).x := :old.x;
27 v_dblogon(v_dblogon.count).y := :old.x;
28 v_dblogon(v_dblogon.count).created := :old.created;
29 v_dblogon(v_dblogon.count).created_by := :old.created_by;
30 end if;
31
32 end before each row;
33
34
35 after statement is
36 begin
37 if deleting then
38 TRI_pkg.delete_separate_transaction(v_audit);
39 raise_application_error (-20002, 'dblogin_user_info table data can n
ot be deleted', true);
40 end if;
41
42 if updating then
43 TRI_pkg.fire_on_insert := false;
44 forall i in 1..v_dblogon.count()
45 insert into t1 values v_dblogon(i);
46 TRI_pkg.fire_on_insert := true;
47 end if;
48 end after statement;
49 end trig_AUDIT;
50 /

Trigger created.

SQL>
SQL> INSERT INTO T1(X,Y) VALUES(10,77);

1 row created.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY
---------- ---------- --------- ----------
10 77 22-NOV-16 DB_ADMIN

SQL> SELECT * FROM AUDIT_TAB;

no rows selected

SQL>
SQL> UPDATE T1 SET X=12;

1 row updated.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY
---------- ---------- --------- ----------
12 77
10 10

SQL> 10 10

SQL> DELETE FROM T1;
DELETE FROM T1
*
ERROR at line 1:
ORA-04084: cannot change NEW values for this trigger type
ORA-06512: at "DB_ADMIN.TRIG_AUDIT", line 6
ORA-04088: error during execution of trigger 'DB_ADMIN.TRIG_AUDIT'


SQL> SELECT * FROM AUDIT_TAB;

no rows selected

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY
---------- ---------- --------- ----------
12 77
10 10

SQL>


Issue #1: When I delete T1 table, throws an error.
Issue #2: When I update T1 table, it is not adding previous values of CREATED, CREATED_BY for
newly inserted row nor carrying it for old row.

Appreciate your help.

Connor McDonald
November 23, 2016 - 5:16 am UTC

I think you might have a bug in 11.1.0.6 there - I just tested this in 11.2.0.4 and 12.1.0.2 and the results are as expected.

Follow up of Compound Trigger

Priya, November 23, 2016 - 2:40 pm UTC

This is my Oracle version SQL*Plus: Release 11.1.0.6.0 - Please suggest any possible ways which are suitable for my Oracle DB version.

I'm worried about the effort we spent for this.
Connor McDonald
November 24, 2016 - 4:26 am UTC

Suggestion 1)

Dont use outdated or unsupported versions of software

Suggestion 2)

Dont use a compound trigger then. Break it up into 3 triggers in the conventional sense. Use a package to hold the state.

Some pseudo-code below to get you started

create or replace
package audit_pkg is
  type t_dblogin is table of t1%rowtype index by pls_integer;
  type t_audit is table of audit_tab%rowtype index by pls_integer;

  v_dblogon audit_pkg.t_dblogin;
  v_audit audit_pkg.t_audit;
   
  fire_on_insert boolean := true;
   
  procedure delete_separate_transaction(p_audit t_audit);
end;
/

create or replace
package body audit_pkg is
  procedure delete_separate_transaction(p_audit t_audit) is
    pragma autonomous_transaction;
  begin
   
    forall j in 1..p_audit.count()
      insert into audit_tab values p_audit(j);
    commit;
  end;
end;
/

create or replace trigger trig1
before delete or insert or update  on t1
begin
  audit_pkg.v_dblogon.delete;
  audit_pkg.v_audit.delete;
end;
/

create or replace trigger trig1
before delete or insert or update  on t1
for each row
begin
  if deleting then
    audit_pkg.v_audit(audit_pkg.v_audit.count+1).action := 'delete';
    audit_pkg.v_audit(audit_pkg.v_audit.count).who := user;
    audit_pkg.v_audit(audit_pkg.v_audit.count).dte := sysdate;
    audit_pkg.v_audit(audit_pkg.v_audit.count).x := :old.x;
    audit_pkg.v_audit(audit_pkg.v_audit.count).y := :old.y;
  end if; --close if for deleting
   
  if inserting then
    if audit_pkg.audit_pkg.fire_on_insert  then
      :new.created :=sysdate;
      :new.created_by := user;
    end if;
  end if; --close if for inserting
   
  if updating then
    audit_pkg.v_dblogon(audit_pkg.v_dblogon.count+1).x := :old.x;
    audit_pkg.v_dblogon(audit_pkg.v_dblogon.count).y := :old.x;
    audit_pkg.v_dblogon(audit_pkg.v_dblogon.count).created := :old.created;
    audit_pkg.v_dblogon(audit_pkg.v_dblogon.count).created_by := :old.created_by;
  end if;
   
end;
/
   
   
create or replace trigger trig1
after delete or insert or update  on t1
begin
  if deleting then
     audit_pkg.delete_separate_transaction(v_audit);
     raise_application_error (-20002, 'dblogin_user_info table data can not be deleted', true);
  end if;
   
  if updating then
    audit_pkg.fire_on_insert := false;
      forall i in 1..audit_pkg.v_dblogon.count()
        insert into t1 values audit_pkg.v_dblogon(i);
    audit_pkg.fire_on_insert := true;
  end if;
end;
/


Follow up of Compound Trigger

Priya, December 01, 2016 - 7:15 pm UTC

Thank you Tom, for such a useful solution.

It works perfects for DELETE, INSERT and UPDATE. But, a small issue with UPDATE is,
1. When a table has a single record , its getting updated and inserting with previous values as a new row. Expected this.
2. When a table has multiple rows, when I update a particular row. It's getting update and inserting with previous values as a new row and also, new records are inserting for all other rows in the table, which I do not.

Please suggest any?
Here is the pseudo code,

BEFORE TRIGGER
UPDATE ON T1
BEGIN
audit_pkg.v_login(audit_pkg.v_login.count+1).ID := :old.ID;
audit_pkg.v_login(audit_pkg.v_oslogin.count).CREATED :=:old.CREATED;
END;

After TRIGGER
UPDATE ON T1
BEGIN
AUDIT_PKG.fire_on_insert := FALSE;
forall i in 1..audit_pkg.v_login.count()
INSERT INTO T1 values audit_pkg.v_login(i);
AUDIT_PKG.fire_on_insert := TRUE;
END;

Connor McDonald
December 02, 2016 - 2:37 am UTC

give us a full test case showing what you are getting and what you want

Follow up of Compound Trigger

Priya, December 02, 2016 - 5:33 pm UTC


SQL> CREATE OR REPLACE package DB_ADMIN.TRI_pkg is
2 type t_dblogin is table of t1%rowtype index by pls_integer;
3 type t_audit is table of audit_tab%rowtype index by pls_integer;
4
5 v_dblogon tri_pkg.T_DBLOGIN;
6 v_audit tri_pkg.t_audit;
7 fire_on_insert boolean := true;
8
9 procedure delete_transaction(p_audit t_audit);
10 end;
11 /

Package created.

SQL>
SQL> CREATE OR REPLACE package body DB_ADMIN.TRI_pkg is
2 procedure delete_transaction(p_audit t_audit) is
3 pragma autonomous_transaction;
4 begin
5
6 INSERT INTO AUDIT_TAB(ACTION,WHO,DTE) VALUES('DELETE','SCOTT',SYSDATE);
7 COMMIT;
8
9 end;
10 end;
11 /

Package body created.

SQL> CREATE OR REPLACE TRIGGER DB_ADMIN.BEFORE_DML_T1
2 BEFORE DELETE OR INSERT OR UPDATE on T1
3 FOR EACH ROW
4
5 DECLARE
6 V_LOGIN_USER NUMBER;
7
8 BEGIN
9 IF DELETING THEN
10 tri_pkg.delete_transaction( tri_pkg.v_audit);
11 raise_application_error (-20002, 'data can not be delet
ed', true);
12 END IF; --CLOSE IF FOR DELETING
13
14 IF INSERTING THEN
15 if audit_pkg.fire_on_insert then
16 :NEW.CREATED := SYSDATE;
17 :NEW.CREATED_BY := USER ;
18 END IF;
19 END IF; -- CLOSE IF FOR INSERTING
20
21 IF UPDATING THEN
22 IF UPPER(:OLD.status) = 'FALSE' THEN
23 RAISE_APPLICATION_ERROR (-20002, 'FALSE RECORDS C
AN NOT BE UPDATED');
24 END IF;
25
26 IF UPPER(:OLD.status) = 'TRUE' THEN
27 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count+1).X := :
old.X;
28 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count).Y := :ol
d.Y;
29 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count).CREATED :
= :old.CREATED;
30 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count).STATUS :
= 'FALSE';
31 END IF;
32 END IF; --CLOSE IF FOR UPDATING
33
34 END BEFORE_DML_T1;
35 /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER AFTER_DML_T1
2 AFTER UPDATE ON T1
3 BEGIN
4 IF UPDATING THEN
5 TRI_PKG.fire_on_insert := FALSE;
6 forall i in 1..TRI_PKG.v_dblogon.count()
7 INSERT INTO T1 values TRI_PKG.v_dblogon(i);
8 TRI_PKG.fire_on_insert := TRUE;
9 END IF;
10 END AFTER_DML_T1;
11 /

Trigger created.

SQL> select * from t1;

no rows selected

SQL> select * from audit_tab;

no rows selected

SQL> INSERT INTO T1 VALUES(1,99,SYSDATE,'SCOTT','TRUE');


SQL> /

1 row created.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
1 99 02-DEC-16 SCOTT TRUE


SQL> DELETE FROM T1;
DELETE FROM T1
*
ERROR at line 1:
ORA-20002: data can not be deleted
ORA-06512: at "DB_ADMIN.BEFORE_DML_T1", line 7
ORA-04088: error during execution of trigger 'DB_ADMIN.BEFORE_DML_T1'


SQL> SELECT * FROM AUDIT_TAB;

ACTION WHO DTE X Y
---------- -------------------- --------- ---------- ----------
DELETE SCOTT 02-DEC-16

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
1 99 02-DEC-16 DB_ADMIN TRUE

SQL>
1 99 02-DEC-16 DB_ADMIN TRUE

SQL> UPDATE T1 SET X=2;

1 row updated.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
2 99 02-DEC-16 DB_ADMIN TRUE
1 99 02-DEC-16 DB_ADMIN FALSE

SQL> UPDATE T1 SET X=3;
UPDATE T1 SET X=3
*
ERROR at line 1:
ORA-20002: FALSE RECORDS CAN NOT BE UPDATED
ORA-06512: at "DB_ADMIN.BEFORE_DML_T1", line 19
ORA-04088: error during execution of trigger 'DB_ADMIN.BEFORE_DML_T1'


SQL> UPDATE T1 SET X=4 WHERE X=2;

1 row updated.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
4 99 02-DEC-16 DB_ADMIN TRUE
1 99 02-DEC-16 DB_ADMIN FALSE
1 99 02-DEC-16 DB_ADMIN FALSE
2 99 02-DEC-16 DB_ADMIN FALSE
2 99 02-DEC-16 DB_ADMIN FALSE

NOTE: DELETE and INSERT works as expected.
NOTE: UPDATE works fine If i try to update when it has single record and false status record.

the issue, if i try to update when it has multiple records. New records are getting inserted for all records in the table which I dont want.

when I wanted to update TRUE status record then new record must be inserted with previous values(status FALSE) and update an existing record value. And it should not insert new records for entire table. Please suggest.

Thank you for your support..!!!

Follow up of DMLTrigger

Priya, December 05, 2016 - 3:54 pm UTC

SQL> CREATE OR REPLACE package DB_ADMIN.TRI_pkg is
2 type t_dblogin is table of t1%rowtype index by pls_integer;
3 type t_audit is table of audit_tab%rowtype index by pls_integer;
4
5 v_dblogon tri_pkg.T_DBLOGIN;
6 v_audit tri_pkg.t_audit;
7 fire_on_insert boolean := true;
8
9 procedure delete_transaction(p_audit t_audit);
10 end;
11 /

Package created.

SQL>
SQL> CREATE OR REPLACE package body DB_ADMIN.TRI_pkg is
2 procedure delete_transaction(p_audit t_audit) is
3 pragma autonomous_transaction;
4 begin
5
6 INSERT INTO AUDIT_TAB(ACTION,WHO,DTE) VALUES('DELETE','SCOTT',SYSDATE);
7 COMMIT;
8
9 end;
10 end;
11 /

Package body created.

SQL> CREATE OR REPLACE TRIGGER DB_ADMIN.BEFORE_DML_T1
2 BEFORE DELETE OR INSERT OR UPDATE on T1
3 FOR EACH ROW
4
5 DECLARE
6 V_LOGIN_USER NUMBER;
7
8 BEGIN
9 IF DELETING THEN
10 tri_pkg.delete_transaction( tri_pkg.v_audit);
11 raise_application_error (-20002, 'data can not be delet
ed', true);
12 END IF; --CLOSE IF FOR DELETING
13
14 IF INSERTING THEN
15 if audit_pkg.fire_on_insert then
16 :NEW.CREATED := SYSDATE;
17 :NEW.CREATED_BY := USER ;
18 END IF;
19 END IF; -- CLOSE IF FOR INSERTING
20
21 IF UPDATING THEN
22 IF UPPER(:OLD.status) = 'FALSE' THEN
23 RAISE_APPLICATION_ERROR (-20002, 'FALSE RECORDS C
AN NOT BE UPDATED');
24 END IF;
25
26 IF UPPER(:OLD.status) = 'TRUE' THEN
27 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count+1).X := :
old.X;
28 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count).Y := :ol
d.Y;
29 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count).CREATED :
= :old.CREATED;
30 tri_pkg.v_dblogon(tri_pkg.v_dblogon.count).STATUS :
= 'FALSE';
31 END IF;
32 END IF; --CLOSE IF FOR UPDATING
33
34 END BEFORE_DML_T1;
35 /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER AFTER_DML_T1
2 AFTER UPDATE ON T1
3 BEGIN
4 IF UPDATING THEN
5 TRI_PKG.fire_on_insert := FALSE;
6 forall i in 1..TRI_PKG.v_dblogon.count()
7 INSERT INTO T1 values TRI_PKG.v_dblogon(i);
8 TRI_PKG.fire_on_insert := TRUE;
9 END IF;
10 END AFTER_DML_T1;
11 /

Trigger created.

SQL> select * from t1;

no rows selected

SQL> select * from audit_tab;

no rows selected

SQL> INSERT INTO T1 VALUES(1,99,SYSDATE,'SCOTT','TRUE');


SQL> /

1 row created.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
1 99 02-DEC-16 SCOTT TRUE


SQL> DELETE FROM T1;
DELETE FROM T1
*
ERROR at line 1:
ORA-20002: data can not be deleted
ORA-06512: at "DB_ADMIN.BEFORE_DML_T1", line 7
ORA-04088: error during execution of trigger 'DB_ADMIN.BEFORE_DML_T1'


SQL> SELECT * FROM AUDIT_TAB;

ACTION WHO DTE X Y
---------- -------------------- --------- ---------- ----------
DELETE SCOTT 02-DEC-16

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
1 99 02-DEC-16 DB_ADMIN TRUE

SQL>
1 99 02-DEC-16 DB_ADMIN TRUE

SQL> UPDATE T1 SET X=2;

1 row updated.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
2 99 02-DEC-16 DB_ADMIN TRUE
1 99 02-DEC-16 DB_ADMIN FALSE

SQL> UPDATE T1 SET X=3;
UPDATE T1 SET X=3
*
ERROR at line 1:
ORA-20002: FALSE RECORDS CAN NOT BE UPDATED
ORA-06512: at "DB_ADMIN.BEFORE_DML_T1", line 19
ORA-04088: error during execution of trigger 'DB_ADMIN.BEFORE_DML_T1'


SQL> UPDATE T1 SET X=4 WHERE X=2;

1 row updated.

SQL> SELECT * FROM T1;

X Y CREATED CREATED_BY STATUS
---------- ---------- --------- ---------- -------
4 99 02-DEC-16 DB_ADMIN TRUE
1 99 02-DEC-16 DB_ADMIN FALSE
1 99 02-DEC-16 DB_ADMIN FALSE
2 99 02-DEC-16 DB_ADMIN FALSE
2 99 02-DEC-16 DB_ADMIN FALSE

NOTE: DELETE and INSERT works as expected.
NOTE: UPDATE works fine If i try to update when it has single record and false status record.

the issue, if i try to update when it has multiple records. New records are getting inserted for all records in the table which I dont want.

when I wanted to update TRUE status record then new record must be inserted with previous values(status FALSE) and update an existing record value. And it should not insert new records for entire table. Please suggest.

Thank you for your support..!!!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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