Thanks for the question, Hima.

Asked: November 18, 2016 - 4:35 pm UTC

Last updated: November 19, 2016 - 4:04 am UTC

Version: 11.1.0.6.0

Viewed 10K+ times! This question is

You Asked

I've come across this issue during trigger code testing today.

My requirement is,
1. If INSERTING then statements in this block should execute on T1.
2. If UPDATING then statements in this block should execute on T1. It has IF ELSE block and it contains INSERT statements. During UPDATE trigger INSERTS of this block has to execute and shouldn't execute insert of #1 recursively, it shouldn't fire.
3. During UPDATE, When a record is updated in a table, a post-event trigger will fire and do inserts. Refer: I put it in last else block or do I need to put it in AFTER UPDATE TRIGGER ?
4. If I go with flg ='N/Y' to differentiate between INSERT in INSERTING block and INSERT in UPDATING block. How can I pass this flag ?

I've given the below pseudo code.Please let me know If you have any questions.

CREATE TRIGGER TRIG
BEFORE INSERT OR UPDATE OF LOGIN_USER, ID ON T1
REFERENCING NEW AS NEW OLD AS OLD
FOR ECH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
V_CNT NUMBER;
BEGIN
IF INSERTING THEN
SELECT COUNT(1)
INTO V_CNT
FROM T1
WHERE LOGIN_USER = :NEW.LOGIN_USER
AND FLAG = 'TRUE';
IF V_CNT >= 1 THEN
RAISE_APPLICATION_ERROR (-2000, 'LOGIN USER ALREADY EXISTS', TRUE);
END IF;
:NEW.ID := LOGIN_SEQ.NEXTVAL;
:NEW.CREATED := SYSDATE;
:NEW.Created_By := USER;
END IF; --CLOSE IF FOR INSERTING

IF UPDATING THEN
IF :OLD.FLAG = 'INACTIVE' THEN
RAISE_APPLICATION_ERROR (-2000, 'INACTIVE RECORDS', TRUE);
ELSE IF :OLD.FLAG = 'FALSE' THEN
INSERT STATEMENT....
ELSE
INSERT STATEMENT.....
END IF;
END IF;
END IF; --CLOSE IF FOR UPDATING

END;
/

and Connor said...

You cannot perform DML in an 'for each row' trigger on the same table that you are triggering on.

It has to be done in a statement level trigger.

Search AskTom for "mutating table" for reasons. In this case, use a compound trigger to store the data you need to store at row level, and insert it after-statement. Pseudo code is:

pkg spec P
  fire_on_insert boolean := true;

compound trigger
  type array is table of t1%rowtype;
  r  array;

  for each row
    if updating then
       store the row in 'r';  (will be used later to insert)

    if inserting then
       if pkg.fire_on_insert then
         --
         -- normal trigger code for insert
       else
         -- do nothing because this is an insert
         -- that has come from our internal update
       end if;

  after statement
     pkg.fire_on_insert  := false;
     for i in 1 .. r.count
        insert into t1 values r(i);
     pkg.fire_on_insert  := true;

Rating

(1 rating)

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

Comments

Thank you.

Hima, November 20, 2016 - 6:24 pm UTC

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 trigger.

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;
/

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thank you.

More to Explore

PL/SQL demos

PL/SQL docs

Inserts inside before update trigger

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thank you.

More to Explore

PL/SQL demos

PL/SQL docs