CODEWITHSUNDEEP
oracle-databasetriggers
user162139
user162139

Reputation: 11

How to get the trigger the way logic wants ?

I am trying to create trigger which fires and store results in aud table when any new row has been updated or inserted in the source table. I am using below script to create that trigger: 

CREATE OR REPLACE TRIGGER abc.E_M_IU_T
BEFORE INSERT OR UPDATE
ON abc.E_A_R_L_T
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
V_ID NUMBER;
BEGIN
SELECT MDM_SEQ_NBR.NEXTVAL INTO V_ID FROM dual;
IF 
UPDATING AND 
:Old.Row_Updater_Nam !='M_S_U' OR :Old.Row_Updater_Nam!='S_S_U' then
BEGIN 
            Insert Into abc.aud
                      ( MDM_IUD_SEQ_NBR
                       ,ENTITY_ID
                       ,ENTITY_TYPE_CD
                       ,DATA_CHANGE_CD
                       ,TABLE_NAME
                       ,PRIMARY_KEY_COL
                       ,SECONDARY_KEY_COL
                       ,PRIMARY_KEY_VAL
                       ,ROW_PROCESS_CD
                       ,ERROR_MSG
                       ,ROW_INSERT_DT
                       ,ROW_UPDATE_DT
                       ,ROW_UPDATER_NAM)
                Values ( V_ID
                       ,:Old.E_ID
                       ,:Old.E_CD
                       ,'C'
                       ,'E_A_R_L_T'
                       ,NULL
                       ,NULL
                       ,NULL
                       ,'N'
                       ,NULL
                       ,SYSDATE
                       ,NULL
                       ,'E_M_IU_T'); 
END; 
elsif INSERTING AND 
:New.Row_Updater_Nam !='M_S_U' OR  :New.Row_Updater_Nam !='S_S_U'  then
BEGIN 
            Insert Into abc.aud
                      ( MDM_IUD_SEQ_NBR
                       ,ENTITY_ID
                       ,ENTITY_TYPE_CD
                       ,DATA_CHANGE_CD
                       ,TABLE_NAME
                       ,PRIMARY_KEY_COL
                       ,SECONDARY_KEY_COL
                       ,PRIMARY_KEY_VAL
                       ,ROW_PROCESS_CD
                       ,ERROR_MSG
                       ,ROW_INSERT_DT
                       ,ROW_UPDATE_DT
                       ,ROW_UPDATER_NAM)
                Values ( V_ID
                       ,:New.E_ID
                       ,:New.E_CD
                       ,'C'
                       ,'E_A_R_L_T'
                       ,NULL
                       ,NULL
                       ,NULL
                       ,'N'
                       ,NULL
                       ,SYSDATE
                       ,NULL
                       ,'E_M_IU_T'); 

END; 
END IF;
END;
/

I am able to create the trigger successfully. But when I am inserting/updating any records in source table, it writes to aud table regardless of the value of row_updater_nam column. I have used != 'm_s_u' and != 's_s_u' in the trigger script, but it seems like it is ignoring it. Can anyone help me to get it resolved? If you feel my trigger script is not accurate, please advise as well.

Thank You

Upvotes: 1

Views: 33

Answers (1)

Hilarion
Hilarion

Reputation: 870

It seems like a simple mistake in the logical operators used. You have

IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' OR :Old.Row_Updater_Nam != 'S_S_U' THEN

While you should be using AND instead of OR:

IF UPDATING AND :Old.Row_Updater_Nam != 'M_S_U' AND :Old.Row_Updater_Nam != 'S_S_U' THEN

The same goes for INSERTING. Is:

ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' OR :New.Row_Updater_Nam !='S_S_U' THEN

Should be:

ELSIF INSERTING AND :New.Row_Updater_Nam != 'M_S_U' AND :New.Row_Updater_Nam !='S_S_U' THEN

I'm assumint that ROW_UPDATER_NAM column is not nullable. Otherwise you may have issues with those conditions, when it is NULL.

Upvotes: 1

Related Questions