ORACLE SQL: After Update Trigger

Question

Problem Statement:

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.

The query I have at the moment is:

CREATE OR REPLACE TRIGGER  trigger_contact_af_update
AFTER UPDATE ON contact
FOR EACH ROW
BEGIN
INSERT INTO contact_log_history (org_name, action) Values (:OLD.org_name, 'After_Update_Contact');
END;

But it's not working. Can someone please tell me why?

The table contact_log_history has only two columns.

I don't get any error messages. The trigger simply doesn't do anything. — prince
Mmm, would you kindly try : ALTER TRIGGER trigger_name ENABLE; — sagi
Can you show the definition of the contact_log_history table? — Lamar

Fabian Steiner Fabian Steiner · Accepted Answer · 2017-08-28T12:14:49

Using the :OLD variable only works when using a before trigger. I would recommend you to do that in your case.

CREATE OR REPLACE TRIGGER  trigger_contact_af_update
BEFORE UPDATE ON contact
FOR EACH ROW
BEGIN
INSERT INTO contact_log_history (org_name, action) Values (:old.org_name, 'After_Update_Contact');
END;

ORACLE SQL: After Update Trigger

1 Answers