CODEWITHSUNDEEP
sqldatabasepostgresqltriggers
giozh
giozh

Reputation: 10068

Emulate the behavior of INSTEAD OF INSERT trigger on table

I've written a trigger like

CREATE TRIGGER myTrigger
INSTEAD OF INSERT ON myTable
EXECUTE PROCEDURE myFunction();

but I've learned that the INSTEAD OF trigger cannot be applied to a table.

The only way to do it should be by using 

BEFORE OF INSERT

the trigger should retrieve some values (unknown when the user calls the insert function) on the other table and insert this value inside firing query and execute it. How can I do this?

Upvotes: 0

Views: 63

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324475

You would write a PL/PgSQL trigger that:

  • Checks tg_op to determine whether the operation is an INSERT, UPDATE or DELETE; and

  • Based on the operation type, uses the NEW and/or OLD pseudo-variables to construct and run INSERTs, UPDATEs, or DELETEs on the real target tables; then

  • Return NULL so that no action is taken on the original target table.

See the basic documentation on PL/PgSQL triggers.

This is a pretty weird thing to want to do unless you're using table inheritance, though.

Upvotes: 1

Related Questions