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?
2
-
1Yes, that's possible, see e.g. here: wiki.postgresql.org/wiki/Audit_trigger_91plususer330315– user3303152015-06-01 20:04:46 +00:00Commented Jun 1, 2015 at 20:04
-
Thanks for the Link. In my case i have 20 main tables & 20 staging tables. Any event like insert or update on the main tables the data should be inserted/updated on their respective staging tables. Can I accomplish this by creating only one trigger function and call it from different tables?Sam– Sam2015-06-01 20:31:33 +00:00Commented Jun 1, 2015 at 20:31
Add a comment
|
1 Answer
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_NAMEData type name; the name of the table that caused the trigger invocation.
TG_TABLE_SCHEMAData 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_OPData 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:
NEWData 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.
OLDData 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.
