Is it possible capture all records into table A from table B on or after truncating table B

Question

I am trying to write a trigger with reference to Postgres DOC. But its not even allowing to create a trigger base on truncate, tried different approaches but didn't work.

CREATE TRIGGER delete_after_test
AFTER truncate
ON tableA
FOR EACH ROW
EXECUTE PROCEDURE delete_after_test3();

Function:

   CREATE OR REPLACE FUNCTION econnect.delete_after_test3()
   RETURNS trigger
   LANGUAGE plpgsql
   AS $function$
   declare 
  query text;

 begin

insert into econnect.delete_after_test_2 (
"name",
age1,
log_time 
)
values
(
old."name",
old.age1,
CURRENT_TIMESTAMP
 )
 ;

   return old;
END;
   $function$
 ;

Reference: https://www.postgresql.org/docs/current/sql-createtrigger.html

"TRUNCATE will not fire any ON DELETE triggers that might exist for the tables. But it will fire ON TRUNCATE triggers. If ON TRUNCATE triggers are defined for any of the tables, then all BEFORE TRUNCATE triggers are fired before any truncation happens, and all AFTER TRUNCATE triggers are fired after the last truncation is performed and any sequences are reset. The triggers will fire in the order that the tables are to be processed (first those listed in the command, and then any that were added due to cascading)"

You missed this part *In addition, triggers may be defined to fire for TRUNCATE, though only FOR EACH STATEMENT.* — Adrian Klaver, Dec 21 '22 at 15:47
I could not come up with solution using `TRUNCATE`. You will probably need an `ON DELETE` statement trigger to do this. — Adrian Klaver, Dec 21 '22 at 19:09

Adrian Klaver · Answer 1 · 2022-12-21T19:27:59.223

A solution using ON DELETE:

create table delete_test(id integer, fld1 varchar, fld2 boolean);
create table delete_test_save(id integer, fld1 varchar, fld2 boolean);
insert into delete_test values (1, 'test', 't'), (2, 'dog', 'f'), (3, 'cat', 't')


CREATE OR REPLACE FUNCTION public.delete_save()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$

BEGIN
    INSERT INTO delete_test_save SELECT * FROM del_recs;
    RETURN OLD;
END;

$function$

CREATE TRIGGER trg_del_save
    AFTER DELETE ON delete_test referencing OLD TABLE AS del_recs FOR EACH statement
    EXECUTE FUNCTION delete_save ();

delete from delete_test;
DELETE 3

select * from delete_test;
 id | fld1 | fld2 
----+------+------
(0 rows)

select * from delete_test_save;
 id | fld1 | fld2 
----+------+------
  1 | test | t
  2 | dog  | f
  3 | cat  | t

The example uses a transition relation (referencing OLD TABLE AS del_recs) to collect all the deleted records for use in the function. Then it is possible to do the INSERT INTO delete_test_save SELECT * FROM del_recs; to transfer the records to the other table. No, they will not work with a TRUNCATE trigger.

Transition relations are explained here Create Trigger:

The REFERENCING option enables collection of transition relations, which are row sets that include all of the rows inserted, deleted, or modified by the current SQL statement. This feature lets the trigger see a global view of what the statement did, not just one row at a time. This option is only allowed for an AFTER trigger that is not a constraint trigger; also, if the trigger is an UPDATE trigger, it must not specify a column_name list. OLD TABLE may only be specified once, and only for a trigger that can fire on UPDATE or DELETE; it creates a transition relation containing the before-images of all rows updated or deleted by the statement. Similarly, NEW TABLE may only be specified once, and only for a trigger that can fire on UPDATE or INSERT; it creates a transition relation containing the after-images of all rows updated or inserted by the statement.

Is it possible capture all records into table A from table B on or after truncating table B

1 Answers1