CODEWITHSUNDEEP
sqloracle-databaseoracle11gtriggers
Splunk
Splunk

Reputation: 311

Creating an ORACLE Trigger (Practice / Study purposes)

I am trying to create a trigger that fires when updates are made to my 'customer' table. I want the trigger to write the cust_no, cust_name, contact and sysdate to another table called Customer_Changelog.

I don't seem to be having a lot of luck...

This is what I have so far:

CREATE OR REPLACE TRIGGER Customer_Up_Tr
AFTER UPDATE OF cust_name, contact ON N_Customer
FOR EACH ROW
WHEN (OLD.contact <> 1 AND NEW.contact = 1 OR OLD.cust_name <> 1 AND NEW.cust_name = 1)
DECLARE
lv_cust_no NUMBER;
lv_cust_name VARCHAR(20);
lv_contact VARCHAR(20);
BEGIN
SELECT cust_no INTO lv_cust_no FROM N_Customer WHERE cust_no = :OLD.cust_no;
SELECT cust_name INTO lv_cust_name FROM N_Customer WHERE cust_name = :OLD.cust_name;
SELECT contact INTO lv_contact FROM N_Customer WHERE contact = :OLD.contact;
INSERT INTO
Customer_changelog (lv_cust_no,lv_cust_name, lv_contact) VALUES (cust_no, cust_name, contact);
END;
/

It is throwing me these errors:

9/1    PL/SQL: SQL Statement ignored
10/86  PL/SQL: ORA-00984: column not allowed here

If I could be pointed in the right direction I would appreciate it.

Upvotes: 2

Views: 652

Answers (1)

Dba
Dba

Reputation: 6639

You can simply write your trigger like this,

create or replace 
TRIGGER Customer_Up_Tr
     AFTER UPDATE OF cust_name, contact ON N_Customer
     FOR EACH ROW
     WHEN (OLD.contact <> 1 AND NEW.contact = 1 OR OLD.cust_name <> 1 AND NEW.cust_name = 1)
BEGIN     
     INSERT INTO customer_changelog (cust_no, cust_name, contact) VALUES (:OLD.cust_no, :OLD.cust_name, :OLD.contact);
END;

Upvotes: 4

Related Questions