CREATE OR REPLACE TRIGGER "DUPLICATE_FOO" AFTER INSERT ON "FOO" FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into remote_foo values(:new); -- can I do this?
EXCEPTION
-- TODO log somewhere
END;
有沒有一種優雅的方法來創建一個基本上將記錄從一個表復制到另一個表的觸發器？
我想避免必須指定表的欄位，因為這意味著如果架構發生更改，則必須更新觸發器（當然會更新遠程方案）。我還有十幾張桌子。
我發現的所有示例:new.fieldX總是在插入 ( ) 中指定欄位。
uj5u.com熱心網友回復：
這里的關鍵字不是指定列名，對嗎？在我看來，你應該這樣做，因為這是你可以控制它的唯一方法。
遠程方案當然會更新
有點危險。如果沒有發生怎么辦？“當然”有效，直到它不起作用。
示例表（均為空）：
SQL> create table foo (id number, name varchar2(20));
Table created.
SQL> create table remote_foo as select * From foo where 1 = 2;
Table created.
如果您使用的觸發器是一個自治事務，那么它不會看到:new偽記錄（因為這是一個自治事務；對嗎？）；到此觸發器，select * from foo where id = :new.id;不會回傳任何內容并remote_foo保持為空：
SQL> create or replace trigger trg_ai_foo
2 after insert on foo
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 insert into remote_foo select * from foo where id = :new.id;
8 commit;
9 end;
10 /
Trigger created.
SQL> insert into foo (id, name) values (1, 'Littlefoot');
1 row created.
SQL> select * from foo;
ID NAME
---------- --------------------
1 Littlefoot
SQL> select * from remote_foo; --> it remained empty
no rows selected
SQL>
請注意 - 如果您指定了列 - 它會起作用（但這不是您想要的）：
SQL> create or replace trigger trg_ai_foo
2 after insert on foo
3 for each row
4 declare
5 pragma autonomous_transaction;
6 begin
7 insert into remote_foo (id, name) values (:new.id, :new.name);
8 commit;
9 end;
10 /
Trigger created.
SQL> insert into foo (id, name) values (2, 'Bigfoot');
1 row created.
SQL> select * from foo;
ID NAME
---------- --------------------
2 Bigfoot
SQL> select * from remote_foo;
ID NAME
---------- --------------------
2 Bigfoot
SQL>
So, what to do? Switch to a statement-level trigger (instead of a row-level): it doesn't have to be autonomous, but has to have something that will prevent duplicates to be inserted - for example, a NOT EXISTS clause:
SQL> create or replace trigger trg_ai_foo
2 after insert on foo
3 begin
4 insert into remote_foo
5 select * from foo a
6 where not exists (select null from remote_foo b
7 where b.id = a.id);
8 end;
9 /
Trigger created.
SQL> insert into foo (id, name) values (1, 'Littlefoot');
1 row created.
SQL> insert into foo (id, name)
2 select 2, 'Bigfoot' from dual union all
3 select 3, 'anat0lius' from dual;
2 rows created.
Result:
SQL> select * from foo;
ID NAME
---------- --------------------
2 Bigfoot
3 anat0lius
1 Littlefoot
SQL> select * from remote_foo;
ID NAME
---------- --------------------
1 Littlefoot
3 anat0lius
2 Bigfoot
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432947.html
標籤：甲骨文