CODEWITHSUNDEEP
oracle-databasetriggers
Dine
Dine

Reputation: 33

ORACLE Trigger INSERT On UPDATE

All, I am just trying to create a trigger that will pick a whole record from TABLE EMP and insert it in TABLE EMP_ARCHIVE on an UPDATE attempt (As the name suggests, EMP_ARCHIVE Table is just a history table to store the changes made on the mail EMP Table). Both table has the same fields/columns. Following is the trigger i am trying to create. I know there is something wrong but couldn't figure out. It throws error at the '(' following the INSERT command. Any help would be appreciated. Forgive me if there's some fundamental error as i am a newbie to these.

CREATE OR REPLACE TRIGGER Save_EMP_Changes
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
   INSERT INTO EMP_ARCHIVE
   (
      emp_id, emp_name,
      emp_age, emp_sex,
      emp_active
   )
   SELECT 
      :old.emp_id, :old.emp_name,
      :old.emp_age, :old.emp_sex,
      :old.emp_active
   FROM EMP 
   WHERE emp_id = :old.emp_id
END;

Upvotes: 3

Views: 3641

Answers (2)

user2025696
user2025696

Reputation: 159

I know this is a bit old question. But figured i put another idea down for anybody else that comes across this question like i just did. In the past i've done the following when doing the same archiving process.

CREATE OR REPLACE TRIGGER Save_EMP_Changes
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
   INSERT INTO EMP_ARCHIVE
   (
      emp_id, emp_name,
      emp_age, emp_sex,
      emp_active, revision_cnt
   )
   SELECT 
      :old.emp_id, :old.emp_name,
      :old.emp_age, :old.emp_sex,
      :old.emp_active, nvl(max(revision_cnt)+1, 1)
   FROM EMP 
   WHERE emp_id = :old.emp_id
END;

HTH others.

Upvotes: 0

user330315
user330315

Reputation:

No need to select from the table:

CREATE OR REPLACE TRIGGER Save_EMP_Changes
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
   INSERT INTO EMP_ARCHIVE
   (
      emp_id, emp_name,
      emp_age, emp_sex,
      emp_active
   )
   VALUES
   (  :old.emp_id, :old.emp_name,
      :old.emp_age, :old.emp_sex,
      :old.emp_active
   );
END;

Btw: in Oracle 11 you can completely automate this by create an FLASHBACK ARCHIVE for those tables. No trigger or any other hassle.

Upvotes: 6

Related Questions