CODEWITHSUNDEEP
postgresqltriggers
Sam
Sam

Reputation: 23

Dynamic trigger function to be called from multiple tables POSTGRESQL

I am new to PostgreSQL and wondering if there is a way to create one trigger function for insert and update operations which can be called from multiple tables?

Upvotes: 2

Views: 1621

Answers (1)

Tom-db
Tom-db

Reputation: 6868

Yes, you can create one trigger procedure and call it from different tables. From within the trigger procedure you can access several special variables which provide metadata about the table which called the trigger i.e.TG_TABLE_NAME and TG_TABLE_SCHEMA. Using those metadata you can accurately define what the trigger have to do depending on which table called it.

From the documentation:

TG_TABLE_NAME Data type name; the name of the table that caused the trigger invocation.

TG_TABLE_SCHEMA Data type name; the name of the schema of the table that caused the trigger invocation.

The variable TG_OP provide the operation which caused the trigger to be called:

TG_OP Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

Other very useful special variables are NEW and OLD. They contain the old and the new data which are changed by the database operation:

NEW Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.

OLD Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is unassigned in statement-level triggers and for INSERT operations.

Upvotes: 3

Related Questions