ORACLE Trigger INSERT On UPDATE

3
votes

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;
2
oracletriggers
Your syntax error is that the Select needs parenthesis around it. a_horse_with_no_name has provided the correct answer, though. You don't require the SELECT at all. - Mikezx6r
Including the actual error message (the ORA-xxxxx error number along with the error text) will generally be helpful. Sometimes the error will jump out at us without that but other times the error is very useful information. - Justin Cave

2 Answers

6
votes

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.

0
votes

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.