[oracle]在更新表 A 中之前插入行到表 B

标签: sql Oracle
发布时间: 2017/4/15 18:51:08
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有个表称为 applications 和一张桌子被称为 application_history 。我想要保留的应用程序的历史,知道使用触发器,每当在获取更新的行的 applications 要复制到的行的表 application_history 它实际上更新之前。

此刻,我写出这段代码从另一所以后︰

create or replace 
trigger APPLICATION_UPDATE_TRG
BEFORE UPDATE ON TBL_APPLICATIONS 
FOR EACH ROW
DECLARE
  CURSOR curAppHistory IS
    SELECT record_number, job_id, submitted_date, status_id, id
      FROM tbl_application
      WHERE id = :old.id;
  vRowAppHistory curAppHistory%ROWTYPE;
BEGIN
  OPEN curAppHistory;
  FETCH curAppHistory INTO vRowAppHistory;
  CLOSE curAppHistory;
  INSERT INTO tbl_application_history
  (record_number, job_id, submitted_date, status_id, application_id)
  VALUES (vRowAppHistory.record_number, vRowAppHistory.job_id, vRowAppHistory.submitted_date, 
  vRowAppHistory.status_id, vRowAppHistory.id);
END;

然而,它不能正确地编译。SQL 开发人员将扔出 3 关于未正确结束的命令和语句被忽略的错误。

这样做的正确方法是什么?

编辑︰ 错误︰

Error(2,10): PLS-00341: declaration of cursor 'CURAPPHISTORY' is incomplete or malformed
Error(3,5): PL/SQL: SQL Statement ignored
Error(4,12): PL/SQL: ORA-00942: table or view does not exist
Error(6,18): PL/SQL: Item ignored
Error(9,3): PL/SQL: SQL Statement ignored
Error(9,28): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(11,3): PL/SQL: SQL Statement ignored
Error(14,29): PLS-00320: the declaration of the type of this expression is incomplete or malformed
Error(14,44): PL/SQL: ORA-00984: column not allowed here

解决方法 1:

Error(4,12): PL/SQL: ORA-00942: table or view does not exist

一个错字?您指定两个不同的表。

BEFORE UPDATE ON TBL_APPLICATIONS

FROM tbl_application

不管怎么说,你会得到 ORA-04091 与此触发器。类似的测试用例︰

SYSTEM@dwal> create table t (key number primary key, value varchar2(10));

Table created.

SYSTEM@dwal> insert into t values (1, 'abcdef');

1 row created.

SYSTEM@dwal> insert into t values (2, 'ghijkl');

1 row created.

SYSTEM@dwal> commit;

Commit complete

SYSTEM@dwal> ed
Wrote file S:\\tools\buffer.sql

  1  create or replace trigger tt
  2  before update on t for each row
  3  declare
  4  cursor c is
  5    select key, value
  6      from t
  7     where key = :old.key;
  8  v c%rowtype;
  9  begin
 10    open c;
 11    fetch c into v;
 12    close c;
 13    dbms_output.put_line(v.value);
 14* end;
09:58:51 SYSTEM@dwal> /

Trigger created.


SYSTEM@dwal> update t set value = '123';
update t set value = '123'
       *
ERROR at line 1:
ORA-04091: table SYSTEM.T is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.TT", line 3
ORA-06512: at "SYSTEM.TT", line 8
ORA-04088: error during execution of trigger 'SYSTEM.TT'

你或许应该这样做︰

INSERT INTO tbl_application_history 
(record_number, job_id, submitted_date, status_id, application_id)
VALUES 
(:old.record_number, :old.job_id, :old.submitted_date, :old.status_id, :old.id);

整个触发器将单个插入语句在这种情况下︰

SYSTEM@dwal> create table t_log (key number, value varchar2(10));

Table created.

SYSTEM@dwal> ed
Wrote file S:\\tools\buffer.sql

  1  create or replace trigger tt
  2  before update on t for each row
  3  begin
  4    insert into t_log values (:old.key, :old.value);
  5* end;
SYSTEM@dwal> /

Trigger created.

SYSTEM@dwal> update t set value = '123';

2 rows updated.

SYSTEM@dwal> commit;

Commit complete.

SYSTEM@dwal> select * from t_log;

       KEY VALUE
---------- ----------
         1 abcdef
         2 ghijkl


SYSTEM@dwal> select * from t;

       KEY VALUE
---------- ----------
         1 123
         2 123
赞助商