ORACLE 在更新时触发 INSERT

发布于 2024-10-19 17:47:22 字数 601 浏览 3 评论 0原文

全部，我只是想创建一个触发器，该触发器将从 TABLE EMP 中选取整个记录，并在 UPDATE 尝试时将其插入到 TABLE EMP_ARCHIVE 中（顾名思义，EMP_ARCHIVE 表只是一个历史表，用于存储对邮件 EMP 表所做的更改））。两个表具有相同的字段/列。以下是我试图创建的触发器。我知道有什么问题，但无法弄清楚。它会在 INSERT 命令后的“(”处引发错误。任何帮助将不胜感激。如果有一些基本错误，请原谅我，因为我是这些新手。

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;

原文

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;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

漆黑的白昼 2024-10-26 17:47:22

无需从表中进行选择：

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;

顺便说一句：在 Oracle 11 中，您可以通过为这些表创建闪回归档来完全自动化此操作。没有触发器或任何其他麻烦。

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 原文

戒ㄋ 2024-10-26 17:47:22

我知道这是一个有点老的问题。但我想我为其他像我一样遇到这个问题的人提出了另一个想法。过去，我在执行相同的归档过程时执行了以下操作。

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;

其他人。

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.

回复收藏 0 原文

~没有更多了~