How to duplicate records with triggers?-CodePudding

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;

Is there an elegant way to create a trigger that basically duplicates a record from one table to another?

I would like to avoid having to specify the fields of the table since it will mean that the trigger would have to be updated in case there are changes in the schema (the remote scheme would be updated of course). I have like a dozen of tables more.

All the examples I have found always specify the fields in the insert (:new.fieldX).

CodePudding user response：

The keyword here is NOT to specify column names, right? In my opinion, you should because that's the only way you can control it.

the remote scheme would be updated of course

is kind of dangerous. WHAT IF it doesn't happen? "Of course" works until it does not work.

Sample tables (both are empty):

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.

If you use a trigger which is an autonomous transaction, then it won't see :new pseudorecord (as this is an autonomous transaction; right?); to this trigger, select * from foo where id = :new.id; won't return anything and remote_foo remains empty:

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>

Note that - if you specified columns - it would work (but that's not what you wanted):

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>