Postgresql: detecting which foreign key triggered the "on before delete" trigger

Question

Let T be a table with two columns: a and b, which reference, respectively, to tables A and B. Such references are of type "delete cascade", so if a row from A or B is deleted, the row in T which matches the origin reference will be also deleted.

Now, I want to set a trigger "on before delete row" on T: is there any way to detect which of the reference triggered the row deletion in T? In other words: can I know if the trigger was triggered by cascading from A or from B?

Thank you.

EDIT

OK, I've simplified the problem. I have the following tables:

users:

• id: integer SERIAL primary key
• name: character varying(128)

validatorchains:

• id: integer SERIAL primary key
• name: character varying(128)

validatorchainslinks:

• chain: integer foreign key validatorchains.id on delete cascade
• user: integer foreign key users.id on delete cascade
• next: integer foreign key users.id on delete set null
• prev: integer foreign key users.id on delete set null

The code of my "on before delete" trigger on validatorchainslinks is:

BEGIN   
   UPDATE validatorchainslinks SET next = OLD.next WHERE next = OLD.user;
   UPDATE validatorchainslinks SET prev = OLD.prev WHERE prev = OLD.user;
   RETURN OLD;
END;

So, the purpose is to create a linked list of users that can validate some sort of operation. In order to validate the operation, all users in the chain must agree. The problem arises when maintaining the linked list when a user is deleted. The trigger code above successfully relinks the elements of the chain. But, what happens if the deletion is triggered by deleting a row in validatorchains? I don't want the trigger to do the UPDATE stuff, but skip it and let the system delete all the rows that reference to the corresponding validator chain.

Hope this helps.

Answer 1

You can do this with a little work-around.

Since PG does not give you any relevant information in the trigger function that fires on the cascaded DELETE (in validatorchainslinks) you should record this information somewhere, immediately prior to the foreign record (in usersor validatorchains) being deleted. So you can write a BEFORE DELETE trigger that updates all records in validatorchainslinks with a value that you can then check in the trigger function of the latter table. This would require you to add a field to the table of course.

Alternatively, you can create a separate table that lists records from tables that are about to be deleted and any trigger function can then review that information. Write that table in a BEFORE DELETE trigger and remove the record in an AFTER DELETE trigger on the same table. This would be the more elegant solution if the same pattern (cascaded deletes) happens among multiple tables.

Cascaded deletes happen in a single transaction so you should be protected from race conditions.