Emulate the behavior of INSTEAD OF INSERT trigger on table

Question

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?

If you're going to post multiple questions in rapid succession please link back to your earlier questions. You should also always mention your PostgreSQL version in your questions. — Craig Ringer
– Craig Ringer, Commented Aug 11, 2013 at 10:00

Craig Ringer · Accepted Answer · 2013-08-11 09:58:03Z

1

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.

answered Aug 11, 2013 at 9:58

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Emulate the behavior of INSTEAD OF INSERT trigger on table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related