NEED HELP DEBUGGING THE FOLLOWING TRIGGER: CREATE OR REPLACE TRIGGER F_Trigger A
ID: 664077 • Letter: N
Question
NEED HELP DEBUGGING THE FOLLOWING TRIGGER:
CREATE OR REPLACE TRIGGER F_Trigger
AFTER INSERT OR UPDATE on Parts
FOR EACH ROW
DECLARE
Dummy INTEGERs;
Mutating_table EXCEPTION;
PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
BEGIN
Dbms_Output.put_Line('Begin Trigger = Update_User_Change);
IF INSERTING
Dbms_Output.put_Line('Inserting part_Num = ':New.Part_Num);
UPDATE part_aud SET ISRT_USER = SYS_CONTEXT ('USERENV', 'SESSION_USER'), isrt_DTTM = current_Timestamp
WHERE Part_Num = :New.Part_Number;
ELSE
Dbms_Output.put_Line('Updating part_Num = ' || :New.Part_Num);
UPDATE part_aud SET UPD_USER = SYS_CONTEXT ('USERENV', 'SESSION_USER'), UPD_DTTM = current_Timestamp
WHERE Part_Num = :New.Part_Numer;
END IF;
EXCEPTION
WHEN Mutating_table THEN
Dbms_Output.put_Line('Mutating Table');
NULL;
END;
Explanation / Answer
CREATE OR REPLACE TRIGGER F_Trigger
AFTER INSERT OR UPDATE on Parts
FOR EACH ROW
DECLARE
BEGIN
Dbms_Output.put_Line('Begin Trigger = Update_User_Change);
IF INSERTING
Dbms_Output.put_Line('Inserting part_Num = ':New.Part_Num);
UPDATE part_aud SET ISRT_USER = SYS_CONTEXT ('USERENV', 'SESSION_USER'), isrt_DTTM = current_Timestamp
WHERE Part_Num = :New.Part_Number;
ELSE
Dbms_Output.put_Line('Updating part_Num = ' || :New.Part_Num);
UPDATE part_aud SET UPD_USER = SYS_CONTEXT ('USERENV', 'SESSION_USER'), UPD_DTTM = current_Timestamp
WHERE Part_Num = :New.Part_Numer;
END IF;
END;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.