在Oracle中使用Merge语句触发 [英] Trigger with Merge statement in oracle

问题描述

下面的代码有什么问题，可以帮助我解决吗?

What is wrong with below code, can some help me to resolve it?

CREATE OR REPLACE TRIGGER TEST_TRI
   AFTER INSERT
   ON TEST1
   FOR EACH ROW WHEN (NEW.COL2 >= '01-MAY-16')
BEGIN
   IF INSERTING
   THEN
        MERGE INTO  TEST_HIST HIST
        USING  TEST1 T1
        ON (T1.NEW.COL1=HIST.COL2)
        WHEN MATCHED THEN
    UPDATE SET 
        HIST.COL5=NEW.COL5
        WHEN NOT MATCHED
        THEN
      INSERT INTO 
           VALUES (:NEW.COL1,:NEW.COL2,:NEW.COL3,:NEW.COL4,:NEW.COL5);                 
   END IF;    
END;
/

错误: 错误(4,3):PL/SQL:忽略了sql stmt 错误(12,14):PL/SQL:ORA-00926:缺少值关键字

Error: Error(4,3):PL/SQL : Sql stmt ignored Error (12,14) : PL/SQL : ORA-00926: MISSING VALUES KEYWORD

感谢您提供的信息，我更改了如下代码，并得到了新的错误.

Thanks for the info, i have changed code like below and getting new error.

CREATE OR REPLACE TRIGGER test_tri
   after INSERT
   ON test1
   FOR EACH ROW WHEN (NEW.col5 >= '01-MAY-16')
   DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   IF INSERTING
   THEN
        MERGE INTO  test_hist hist
        USING  test1 t1
        ON (t1.PACKAGE_ID=hist.PACKAGE_ID)
        WHEN MATCHED THEN
    UPDATE SET 
        hist.col5=t1.col5
        WHEN NOT MATCHED
        THEN
      INSERT (col1,col2, col3, col4, col5) 
           VALUES (:NEW.col1,:NEW.col2,:NEW.col3,:NEW.col4,:NEW.col5);                 
   END IF;  
   COMMIT;
END;
/

在test1表中插入记录时出现以下错误 ORA-30926:无法在源表中获得稳定的行集

While inserting record in test1 table am getting below error ORA-30926: unable to get a stable set of rows in the source table

推荐答案

我怀疑您的合并语句中混合了所有源数据.您只想考虑要插入的行，对吗?

You've got your source data all mixed up in your merge statement, I suspect. You only want to consider the rows being inserted, right?

我认为您的触发器应该类似于:

I think your trigger should be something like:

CREATE OR REPLACE TRIGGER test_tri
   after INSERT
   ON test1
   FOR EACH ROW WHEN (NEW.col5 >= '01-MAY-16')
   DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   IF INSERTING
   THEN
        MERGE INTO test_hist hist
        USING (select :new.package_id, :new.col1, :new.col2, :new.col3, :new.col4, :new.col5
               from   dual) t1
          ON (t1.PACKAGE_ID=hist.PACKAGE_ID)
        WHEN MATCHED THEN
          UPDATE SET hist.col5=t1.col5
        WHEN NOT MATCHED THEN
          INSERT (col1, col2, col3, col4, col5)
          VALUES (t1.col1, t1.col2, t1.col3, t1.col4, t1.col5);
   END IF;  
   COMMIT;
END;
/

如果:new.col5是日期列，则更改:

N.B. if :new.col5 is a date column, then change:

FOR EACH ROW WHEN (NEW.col5 >= '01-MAY-16')

到

FOR EACH ROW WHEN (NEW.col5 >= to_date('01/05/2016', 'dd/mm/yyyy'))

年份有4位数字(我猜你是2016年而不是1916年！).

Years have 4 digits (I've guessed that you meant 2016, and not 1916!).

这篇关于在Oracle中使用Merge语句触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！