CODEWITHSUNDEEP
oracle-databasetriggers
Stephia
Stephia

Reputation: 1

Creating triggers in oracle

Please help me to resolve the below problem

I need to 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 updation 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)

The table structure of contact is as follows:

id is the primary key in contact table

id integer org_name varchar2(255) street_address1 varchar2(255) street_address2 varchar2(255) city varchar2(255) state varchar2(255) postal_code varchar2(255) country_code varchar2(255) last_name varchar2(255) first_name varchar2(255) person_title varchar2(255) phone_country_code integer phone_area_code integer phone_number varchar2(255) email varchar2(255) created_time timestamp

I have created the trigger as below, but while executing it is not returning any error message nor creating the trigger. Please let me know the error in the creation of trigger / the correct statement

CREATE OR REPLACE TRIGGER trigger_contact_af_update
AFTER UPDATE
   ON contact
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO contact_log_history
   (org_name,
     action)
select org_name, 'contact_log_history' 
from contact 
END;

Upvotes: 0

Views: 1516

Answers (2)

Mehdi Javan
Mehdi Javan

Reputation: 1091

You must use :new keyword. Also, you must add ; at the end of insert statement. The corrected version of the trigger is:

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

Upvotes: 0

Vecchiasignora
Vecchiasignora

Reputation: 1315

why you dont use only new updated value? you can insert only updated new value :new.org_name, like this

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

Upvotes: 2

Related Questions