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?
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();
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.
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.
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:
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;
