Triggers, Rules, and Views - PostgreSQL Developer's Guide - page 88

You can easily list the triggers you have created as follows:
warehouse_db=# SELECT * FROM pg_trigger;
The previous command will list all triggers but if you want to see triggers associated
with a particular table, you can do that as well in the following manner:
warehouse_db=# SELECT tgname FROM pg_trigger, pg_class WHERE
tgrelid=pg_class.oid AND relname='warehouse_tbl';
If you want to delete the triggers, use the DROP TRIGGER command as follows:
warehouse_db=# DROP TRIGGER audit_all_ops_trigger ON
warehouse_tbl;
Creating triggers in PL/Perl
Triggers can be written in PL/Perl as well. You will be using the $_TD hash reference
to access the information about a trigger event. $_TD is a global variable. The different
ields of the $_TD hash reference are as follows:
• $_TD->{new}{col} : This is the NEW value of column
• $_TD->{old}{col} : This is the OLD value of column
• $_TD->{event} : This is the type of event, that is, whether it is INSERT ,
UPDATE , or DELETE
• $_TD->{table_name} : This is the name of table on which the trigger is ired
Let's create a simple trigger in PL/Perl. Before this, we will create a table called
tab_perl , with three columns, emp_id , emp_name , and emp_city , as follows:
warehouse_db=# CREATE TABLE tab_perl
(
emp_id INT NOT NULL,
emp_name VARCHAR(10),
emp_city VARCHAR(10)
);
It's always good to insert a few rows in the table to be tested. This is done as follows:
warehouse_db=# INSERT INTO tab_perl VALUES (1, 'Adam', 'Chicago');
warehouse_db=# INSERT INTO tab_perl VALUES (2, 'John', 'Miami');