Create trigger to insert After Update the table

Question

Create a trigger named trigger_contact_af_update that is triggered whenever the contact table is updated. This trigger will insert the org_name and action into the table contact_log_history after the update of contact details. The action name in the affected log table contact_log_history is 'After_Update_Contact'.

Hints:

Trigger name : trigger_contact_af_update
Table name : contact_log_history
Field names : org_name,action
Action : 'After_Update_Contact'.

The table structure of contact_log_history is as follows:

org_name Varchar(30)
action Varchar(30)

I wrote the below trigger but no error or trigger created.

CREATE OR REPLACE TRIGGER trigger_contact_af_update AFTER UPDATE 
    ON contact_log_history FOR EACH ROW 
DECLARE 
    org_name VARCHAR(30);
    action VARCHAR(30);
BEGIN 
    if (:new.action == 'After_Update_Contact') 
    then 
        INSERT INTO contact_log_history (org_name, action) 
        values (:new.org_name, :new:action);
    end if ;
END;

Your assignment says to track changes in the contact table but for some reason you have built the trigger on contact_log_history. You might get more success if you built it on the right table. — APC
and replace double equation (==) with single(=) for :new.action == 'After_Update_Contact' — Barbaros Özhan
Tried the below, but still trigger is not created. CREATE OR REPLACE TRIGGER contact_af_update AFTER UPDATE ON contact FOR EACH ROW DECLARE org_name VARCHAR(30); action VARCHAR(30); BEGIN if (:new.action = 'After_Update_Contact') then INSERT INTO contact_log_history (org_name, action) values (:new.org_name, :new:action); end if ; END; — sruthi
Please check my answer and accept it if it helped you or give a relevant feedback(comments) for the given solution. Please read : stackoverflow.com/help/someone-answers to understand why it's important. — Kaushik Nayak

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-07-02T03:51:21

You should use

SHOW ERRORS

or

select * from user_errors;

to check the errors during compilation for procedures/functions/triggers.

Here's a working trigger after fixing the issues and avoiding the variables.

CREATE  OR replace TRIGGER contact_af_update AFTER 
UPDATE 
ON contact FOR EACH ROW 
DECLARE 
BEGIN 
  IF (:new.action = 'After_Update_Contact') THEN 
    INSERT INTO contact_log_history 
                ( 
                            org_name, 
                            action 
                ) 
                VALUES 
                ( 
                            :new.org_name, 
                            :new.action 
                ); 

  END IF ; 
END;
/

Demo

Create trigger to insert After Update the table

2 Answers