Creating triggers in oracle

Question

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;

@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
– Mehdi Javan, Commented Aug 30, 2017 at 12:41
@Stephia How do you run your Create Trigger statement? Which software do you use? — Mehdi Javan
– Mehdi Javan, Commented Aug 30, 2017 at 12:48
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
– Stephia, Commented Sep 6, 2017 at 5:27

Vecchiasignora · Accepted Answer · 2017-08-30 06:17:18Z

2

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;

answered Aug 30, 2017 at 6:17

Vecchiasignora

1,3157 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mehdi Javan · Accepted Answer · 2017-08-30 07:17:54Z

0

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;

edited Aug 30, 2017 at 7:17

answered Aug 30, 2017 at 6:20

Mehdi Javan

1,0916 silver badges26 bronze badges

5 Comments

ShoeLace Over a year ago

the select from dual is redundant in this scenario

Stephia Over a year ago

Tried below 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; but trigger is not creating.. Is there any other syntax

Mehdi Javan Over a year ago

@Stephia Would you please add DDL of contact and contact_log_history tables to the question?

Stephia Over a year ago

The table structure of contact_log_history is as follows: org_name varchar(30), action varchar(30) The table structure of contact table is id integer, org_name varchar2(255), street_address1 varchar2(255).,,,,, and there are 13 other columns in contact table

Mehdi Javan Over a year ago

@Stephia Edit your question and add complete tables structures there. Your problem may be related to structures.

Collectives™ on Stack Overflow

Creating triggers in oracle

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related