I have a trigger function in postgresql which will insert rows in the audit table on INSERT, UPDATE and DELETE operations. In my tables, there is a column called audit_id and I need to write the ID of inserted audit row in this field. This is my function
CREATE OR REPLACE FUNCTION my_audit_trigger()
RETURNS trigger LANGUAGE plpgsql
AS $function$
declare
audit_pk bigint;
begin
IF TG_OP = 'INSERT'
THEN
INSERT INTO audit.table_audit (rel_id, table_name, operation, after)
VALUES (TG_RELID, TG_TABLE_NAME, TG_OP, to_jsonb(NEW)) returning id into audit_pk;
NEW.audit_id := audit_pk;
RETURN NEW;
ELSIF TG_OP = 'UPDATE'
THEN
IF NEW != OLD THEN
INSERT INTO audit.table_audit (rel_id, table_name, operation, before, after)
VALUES (TG_RELID, TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW)) returning id into audit_pk;
END IF;
NEW.audit_id := audit_pk;
RETURN NEW;
ELSIF TG_OP = 'DELETE'
THEN
INSERT INTO audit.table_audit (rel_id, table_name, operation, before)
VALUES (TG_RELID, TG_TABLE_NAME, TG_OP, to_jsonb(OLD)) returning id into audit_pk;
OLD.audit_id := audit_pk;
RETURN OLD;
END IF;
end;
$function$;
As a result, when inserting or updating my table rows, I get back the audit id of the corresponding operation, but when I run DELETE command, I get back the audit ID of the previous operation, not of the DELETE itself. So I guess the problem is in OLD.audit_id := audit_pk;
More specifically, I run for example INSERT INTO table VALUES (this, that) RETURNING audit_id and I get back audit_id of the INSERT operation.
After, when running DELETE FROM table WHERE id = sth RETURNING audit_id I get audit_id of the INSERT operation, not of the DELETE.
Any help is appreciated, thank you.
P.S. This is how I create trigger
CREATE TRIGGER table_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON table
FOR EACH ROW
EXECUTE PROCEDURE my_audit_trigger();
DELETE?idfield, which is returned intoaudit_pk, obtains its value, as that field is not specified in the insert statement. The issue must lie there - where is that value coming from, and why is it being set to an unexpected value? There isn't a problem I can see in the trigger itself, it must be in the actual data in that audit table. Bearing in mind it's not even the table that the insert or update statements write to, so how is that value being set in theaffiliate_audittable? Only you have access to the info to figure that out.