How to make PostgreSQL insert a row into a table when deleted from another table?

Question

We have an application, which will delete a row from a table based on user requests. I cannot change the application code. However, I want to insert a row into another table (kinda like a journal log) with information from a few other tables based on information of the row that is being deleted.

How do I achieve this within PostgreSQL?

Answer 1

Write a trigger function. Something like this:

CREATE OR REPLACE FUNCTION trg_backup_row()
  RETURNS trigger AS
$BODY$
BEGIN

INSERT INTO other_tbl
SELECT (OLD).*, t.other_col                -- all columns of from old table
-- SELECT OLD.col1, OLD.col2, t.other_col  -- alternative: some cols from old tbl
FROM   third_tbl t
WHERE  t.col = OLD.col  -- link to third table with info from deleted row
AND    <unique_condition_to_avoid_multiple_rows_if_needed>;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

And a trigger ON DELETE. Like this:

CREATE TRIGGER delaft
  AFTER DELETE
  ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_backup_row();

Key elements

• Best make it a trigger AFTER DELETE and FOR EACH ROW.

• To return all columns from the old table use the syntax (OLD).*. See the manual about accessing composite types. Alternatively OLD.* is valid syntax, too, because OLD is added to the FROM clause implicitly. For a VALUES expression it would have to be (OLD).*, though. Like:

  INSERT INTO other_tbl
  VALUES((OLD).*, some_variable)
  

• You can include values from any other table like I demonstrate. Just make sure to get a single row, or you create multiple entries.

• As the trigger fires AFTER the event, the function can RETURN NULL.

---

About visibility

In response to @couling's watchful comment.

While foreign keys can be declared as DEFERRED, this will only defer the integrity check, not the deletion itself. Rows that are deleted in triggers executed before the one at hand or by ON DELETE CASCADE foreign keys will not be visible any more at the time this AFTER DELETE trigger is called. (It all happens in one transaction obviously. None of these details matter for other transactions, which will see all or none of the effects. Refer to the manual for more about the MVCC model and transaction isolation.)

Therefore, if you want to include values from rows depending in such a way in your INSERT, be sure to call this trigger before those rows get deleted.

You may have to you make this trigger BEFORE DELETE.

Or it can mean that you have to order your triggers accordingly, BEFORE triggers come before AFTER triggers, obviously. And triggers at the same level are executed in alphabetical order.

However, as long as I am super precise here, I might also add that changes made to the row (or depending rows) in other BEFORE triggers are also only visible if those are called before this one.

My advice to make it an AFTER trigger was because it is less prone to complications and cheaper if other trigger might cancel (roll back) the DELETE half way through the operation - as long as none of the above applies.

Answer 2

You may want to use functions that I wrote for keeping historical data. Short decription:

Historical data are kept in separate schema named audit. So first step would be creating this schema:

CREATE SCHEMA audit;

In audit schema one can find exact copy of tables from public, that are created dynamically when first change in data in public schema occurs. So, before first use of database audit schema remains empty until user will do his first insert into one of the tables.

Function _audit_table_creator(name) is copying then structure of the table from public schema and creates the same table in audit schema with some additional columns, that I called ‘audit stamp’. Audit stamp keeps the informations about:

• time when record was deleted (shift_time),
• user that made the deletion (who_altered),
• ‘DELETE’ stamp (alter_type), and
• column that has been changed - for update operations only (changed_columns);

I think the biggest advantage of this solution is that composite primary keys are supported (function _where_clause_creator(text[]) creates proper where clause for table called by trigger by concatenating strings in the right order);

Viewing historical records:

Everytime we want to retrieve archival data, we have to use aliases, i.e. to retrieve historical data about user whos user_id = 5 one have to write:

SELECT * FROM audit.users WHERE user_id = 5; 

So the same queries can be used in both schemas but to retrieve historical data one have to add ‘audit.’ before table name.

You may want to create delete triggers automatically for all tables in database at once, if you do you can just do the query:

SELECT * FROM  audit_gen_triggers();

---

The main function:

CREATE OR REPLACE FUNCTION audit_delete() 
  RETURNS trigger AS
$BODY$DECLARE
t_name text;                 
query_op text;              
primary_keys text;           
c record;
key_arr text;
keys_arr text;
p_r text;

    BEGIN

    t_name := 'audit.' || TG_TABLE_NAME;
         IF NOT EXISTS(SELECT 1 FROM pg_tables WHERE schemaname = 'audit' AND 
                       tablename = TG_TABLE_NAME) THEN
           EXECUTE 'SELECT _audit_table_creator(table_name := ($1)::name)' 
           USING TG_TABLE_NAME;
        END IF; 

        FOR c IN SELECT pg_attribute.attname
                      FROM pg_index, pg_class, pg_attribute 
                      WHERE 
                      pg_class.oid = TG_TABLE_NAME::regclass AND
                      indrelid = pg_class.oid AND
                      pg_attribute.attrelid = pg_class.oid AND
                      pg_attribute.attnum = ANY(pg_index.indkey) AND 
                      indisprimary LOOP

               key_arr := c.attname || ', ($1).' || c.attname;
               keys_arr := concat_ws(',', keys_arr, key_arr);
               END LOOP;
               keys_arr := '{' || keys_arr || '}';

        EXECUTE 'SELECT _where_clause_creator(VARIADIC ($1)::text[])' 
        INTO p_r USING keys_arr;
        -- raise notice 'tablica where: %', p_r;

        -- zapisz do tabeli audytowanej wszystkie usuniete wartosci
        query_op := 'INSERT INTO '||  t_name ||
                           ' SELECT NEXTVAL(''serial_audit_' 
                             ||  TG_TABLE_NAME ||'''::regclass),
                             CURRENT_USER, ''' ||  TG_OP || ''',
                             NULL,
                             NOW(),
                             ($1).*
                             FROM ' || TG_TABLE_NAME ||
                             ' WHERE  ' || p_r;
                EXECUTE query_op USING OLD;
        RETURN OLD;
    END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

trigger:

CREATE TRIGGER table_name_delete_audit
  BEFORE DELETE
  ON table_name
  FOR EACH ROW
  EXECUTE PROCEDURE audit_delete();

other functions used:

    CREATE OR REPLACE FUNCTION _array_position(anyarray, anyelement)
      RETURNS integer AS
    $BODY$
    SELECT i
       FROM (SELECT generate_subscripts($1, 1) as i, unnest($1) as v) s
      WHERE v = $2
      UNION ALL
      SELECT 0 
      LIMIT 1;
    $BODY$
      LANGUAGE sql STABLE
      COST 100;


CREATE OR REPLACE FUNCTION _audit_table_creator(table_name name)
RETURNS void AS
$BODY$
DECLARE
query_create text; 

BEGIN
query_create := 'DROP TABLE IF EXISTS temp_insert; 
                 DROP TABLE IF EXISTS temp_insert_prepared';
EXECUTE query_create;

query_create := 'DROP SEQUENCE IF EXISTS serial_audit_' ||  table_name;
                 EXECUTE query_create;
query_create := 'CREATE SEQUENCE serial_audit_' || table_name || ' START 1; 
                 ALTER TABLE serial_audit_' || table_name || 
                 ' OWNER TO audit_owner;';
                 EXECUTE query_create;
query_create := 'CREATE TEMPORARY TABLE temp_insert_prepared ( '
                 || table_name || '_audit_id bigint DEFAULT 
                 nextval(''serial_audit_' || table_name || '''::regclass),
                 who_altered text DEFAULT CURRENT_USER,
                 alter_type varchar(6) DEFAULT ''INSERT'',
                 changed_columns text,
                 shift_time timestamp(0) without time zone DEFAULT NOW(),
                 PRIMARY KEY(' || table_name || '_audit_id )) ON COMMIT DROP';

EXECUTE query_create;

query_create := 'CREATE TEMPORARY TABLE temp_insert ON COMMIT DROP AS TABLE 
                ' || table_name;
                EXECUTE query_create;

                query_create := 'CREATE TABLE audit.' || table_name || 
                ' AS SELECT a.*, b.* FROM temp_insert_prepared a, temp_insert b 
                 WITH NO DATA';
                EXECUTE query_create;

    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

CREATE OR REPLACE FUNCTION _where_clause_creator(VARIADIC keys_given text[])
RETURNS text AS
$BODY$
DECLARE
x text;
where_clause text;

BEGIN
FOREACH x IN ARRAY keys_given LOOP
IF ((SELECT _array_position(keys_given, x))%2) <> 0 THEN
where_clause := concat_ws(' AND ', where_clause, x);
ELSE
       where_clause := concat_ws(' = ', where_clause, x);
END IF;
END LOOP;
RETURN where_clause;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100;



CREATE OR REPLACE FUNCTION audit_gen_triggers()
  RETURNS void AS
$BODY$
DECLARE
r record;
query_create text;
BEGIN
FOR r IN SELECT table_name
             FROM information_schema.tables
             WHERE table_schema = current_schema AND
                         table_type = 'BASE TABLE' LOOP

query_create := 'DROP TRIGGER IF EXISTS ' || r.table_name || '_delete_audit ON ' 
                 || r.table_name || ' CASCADE;
                 CREATE TRIGGER ' || r.table_name || '_delete_audit
                 BEFORE DELETE
                 ON ' || r.table_name || '
                 FOR EACH ROW
                 EXECUTE PROCEDURE audit_delete();';

EXECUTE query_create;
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;