Debug the following TRIGGER SQL Statement. Use the Virtual Machine you have been

Question

Debug the following TRIGGER SQL Statement. Use the Virtual Machine you have been provided to do this. List everything that you had to do in order to fix it. Only list the line that was fixed and what you did to it.

The Trigger code is as follows:

CREATE OR REPLACE TRIGGER Final_Triggerafter INSERT OR UPDATE on PartsFOR EACH ROW
DECLARE Dummy INTEGERs; -- used for cursor fetch below Mutating_table EXCEPTION; PRAGMA EXCEPTION_INIT (Mutating_table, -4091);
BEGINDbms_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

Hi Sir, Please find the error and the fix for the same below:-

ERROR:- CREATE OR REPLACE TRIGGER Final_Triggerafter INSERT OR UPDATE

FIX:- CREATE OR REPLACE TRIGGER Final_Triggerafter BEFORE INSERT ..... [Mention the event BEFORE/AFTER]

CREATE OR REPLACE TRIGGER Final_Triggerafter1 BEFORE UPDATE..... [You cannot create multiple triggers in the same line, You need to create 2 different Triggers for INSERT and UPDATE]

ERROR:- DECLARE Dummy INTEGERs;

FIX:- DECLARE Dummy INTEGER; [The Datatype is INTEGER not INTEGERs]

ERROR:- Dbms_Output.put_Line('Begin Trigger = Update_User_Change);

FIX:- Dbms_Output.put_Line('Begin Trigger = Update_User_Change'); [Single Quote was missing]

ERROR:- IF INSERTING

FIX:- IF INSERTING THEN Dbms_Output.put_Line..... [THEN Keyword is missing after IF Condition]

ERROR:- Dbms_Output.put_Line('Inserting part_Num = ':New.Part_Num);

FIX:- Dbms_Output.put_Line('Inserting part_Num = ' || :New.Part_Num); [See the Structure inside DBMS_OUTPUT.PUT_LINE()

ERROR:- NULL;

FIX:- Remove this line, we dont need this in EXCEPTION Handling section of PLSQL Block .

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.