Creating triggers in oracle

0
votes

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;
2
oracletriggers
why you dont use only new updated value – Vecchiasignora
@Stephia What version of Oracle do you use? How do you check whether the trigger is created or not? Did you try to run this select statement: select * from user_triggers where trigger_name = 'TRIGGER_CONTACT_AF_UPDATE' ? – Mehdi Javan
@Stephia How do you run your Create Trigger statement? Which software do you use? – Mehdi Javan
Using DBMS editor.. Nothing is displayed after executing the trigger statement. I tried "select * from user_triggers where trigger_name = 'TRIGGER_CONTACT_AF_UPDATE'" .. No rows selected on executing it,, – Stephia
Any updates??Please help – Stephia

2 Answers

2
votes

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;
0
votes

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;