ORACLE Trigger INSERT On UPDATE

Question

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;

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
– Mikezx6r, Commented Mar 2, 2011 at 19:54
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
– Justin Cave, Commented Mar 2, 2011 at 19:54

user330315 · Accepted Answer · 2011-03-02 19:52:04Z

6

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.

answered Mar 2, 2011 at 19:52

user330315

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

9 Comments

Justin Cave Over a year ago

Prior to 11g, Workspace Manager can also be used to automate the tracking of history information.

user330315 Over a year ago

@Justin: Yes, but it's not as easy to query information. With a flashback archive you can do a SELECT * FROM some_table AS OF TIMESTAMP TIMESTAMP '2005-06-28 14:38:12'

user330315 Over a year ago

Did you include the semicolon after the INSERT? With which tool are you running this? In SQLPlus you need to terminate this with a / on a single line. It might be a good idea to copy & paste (as an edito the question) what you are doing in SQL*Plus in case you use it.

Dine Over a year ago

@a_horse_with_no_name: A quick subquestion. Trigger created using above code inserts two record in the EMP_ARCHIVE table, where i wanted only the old record to be inserted. Did you get what i am saying?

user330315 Over a year ago

@Dine: that trigger will only insert a single row. But this will happen for each and every UPDATE you make. So if you update emp twice, you'll wind up with two rows in emp_archive.

|

user2025696 · Accepted Answer · 2014-04-09 15:41:18Z

0

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.

answered Apr 9, 2014 at 15:41

user2025696

1591 silver badge11 bronze badges

Collectives™ on Stack Overflow

ORACLE Trigger INSERT On UPDATE

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related