oracle Before trigger on DML operations on same table

Question

Requirement is,

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.
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 creation”
IF UPDATING, Required Fields are ID,LOGIN_USER

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

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

o The following fields are populated or overridden with the following values: Active False Event “Audit for data maintenance”

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

• A record with a status of Active = False may not be updated. If so, a new record must be created.

I have written the code and it performs DELETE AND INSERT without any issue.

The problem is with UPDATE,when it hits UPDATING block, INSERTS inside else blocks; here INSERT query is fired by INSERTING block recursively. Which is not supposed to execute when I wanted to run UPDATING block only.

Please suggest, is there any other ways to follow. This is first time Im using trigger code.

CREATE OR REPLACE TRIGGER BEFORE_DML                  
BEFORE DELETE OR INSERT OR UPDATE OF ID,LOGIN_USER ON USER_INFO 
REFERENCING NEW AS NEW OLD AS OLD              
FOR EACH ROW

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;                        
    V_USER          VARCHAR2 (30);          
    V_NODE          VARCHAR2 (50);        
    V_CNT           NUMBER   := 0;       
    V_ACTIVE        VARCHAR2 (10);      

BEGIN        
    SELECT SYS_CONTEXT ('USERENV', 'IP_ADDRESS')        
    INTO V_NODE       
    FROM DUAL;       

    IF  DELETING THEN     
        INSERT        
        INTO AUDIT (IDS,       
                    ACTION,         
                    IP_ADDRESS,         
                    LOGIN,           
                    CREATED        
                    )           
             VALUES(AUDIT_SEQ.NEXTVAL,           
                    'Deletion',        
                    V_NODE,       
                    SYS.LOGIN_USER,         
                    SYSDATE      
                    );         
        COMMIT;    
        RAISE_APPLICATION_ERROR (-20002, 'USER_INFO table data can not be deleted');    
    END IF; --Close IF for DELETING     

    IF INSERTING THEN              

        SELECT COUNT (1)          
        INTO  V_CNT        
        FROM  USER_INFO      
        WHERE LOGIN_USER = :NEW.LOGIN_USER         
        AND   STATUS='TRUE';        

            IF ( V_CNT >=1 )  THEN      
                RAISE_APPLICATION_ERROR (-20002, 'DATABASE LOGIN USER ALREADY EXISTS', TRUE);      
            END IF;     
               :NEW.ID        :=   DBLOGIN_SEQ.NEXTVAL;            
               :NEW.CREATED    :=   SYSDATE;         
               :NEW.ACTION     :=   'Record Creation' ;                
    END IF; --CLOSE IF FOR INSERTING        

    IF UPDATING THEN      

        IF  :OLD.STATUS = 'INACTIVE'          
        THEN    
            RAISE_APPLICATION_ERROR (-20002, 'INACTIVE RECORDS CAN NOT BE  UPDATED');   
        ELSE IF  :OLD.STATUS = 'FALSE'  THEN       
               INSERT INTO USER_INFO(ID,     
                                    STATUS,          
                                    LOGIN_USER,         
                                    CREATED,        
                                    ACTION        
                                    )         
                             VALUES(DBLOGIN_SEQ.NEXTVAL,      
                                    'INVALID',            
                                    :OLD.LOGIN_USER,            
                                    SYSDATE,           
                                    'FALSE RECORD UPDATED '         
                                    );          
                COMMIT;          
        ELSE      
            INSERT INTO USER_INFO(ID,           
                                  STATUS,          
                                  LOGIN_USER,            
                                  CREATED,            
                                  ACTION          
                                  )       
                           VALUES(DBLOGIN_SEQ.NEXTVAL,              
                                  'FALSE',         
                                  :OLD.LOGIN_USER, --NEW.LOGIN_USER         
                                  SYSDATE,      
                                  'Audit for data maintenance'     
                                   );
            COMMIT;    
        END IF;                                               
    END IF;       
    END IF; --CLOSE IF FOR UPDATING              

END BEFORE_DML;
/

Is this a homework assignment or are you trying to solve a real-world problem? You could do something hack-y where you have a package with a public global in_before_dml_trigger variable that you set in your update block and check at the top of your trigger. That would solve the immediate problem but is a royal pain from a maintenance and support perspective. You are doing things in the trigger that will work only in a single-user system as well-- a unique index would actually ensure there is only one row for a login_user in a multi-user system. — Justin Cave

Renato Afonso Renato Afonso · Accepted Answer · 2016-11-21T16:07:53

One way would be to disable the trigger before your inserts, and then enabling it again. Example:

execute immediate 'alter trigger [trigger_name] disable'

oracle Before trigger on DML operations on same table

1 Answers