PostgreSQL trigger (function) examples

postgres=# c warehouse_db
You are now connected to database "warehouse_db" as user "postgres".
warehouse_db=# set search_path ='record';
SET
warehouse_db=# show search_path ;
search_path
-------------
record
(1 row)

warehouse_db=# create table warehouse_tb1(warehouse_id integer not null,warehouse_name text not null,year_created integer,street_address text,city character varying(100),state character varying(2),zip character varying(10),
warehouse_db(# constraint "prim_key" primary key (warehouse_id));
CREATE TABLE
warehouse_db=# insert into warehouse_tb1 (warehouse_id,warehouse_name,year_created,street_address,city,state,zip)values (1,'Mark Corp',2009,'207-F Main Service Road Ease','New London','CT',4321);
INSERT 0 1
warehouse_db=#
warehouse_db=# insert into warehouse_tb1 (warehouse_id ,warehouse_name,year_created,street_address,city,state,zip)values (2,'Bill & Co',2014,'Lilly Road','New London','CT',4321);
INSERT 0 1
warehouse_db=# select warehouse_id,warehouse_name,state from warehouse_tb1;
warehouse_id | warehouse_name | state
--------------+----------------+-------
1 | Mark Corp | CT
2 | Bill & Co | CT
(2 rows)

warehouse_db=# create or replace function warehouse_audit_func() returns trigger as $first_trigger$
warehouse_db$# begin
warehouse_db$# insert into warehouse_audit (wlog_id,insertion_time) values (new.warehouse_id,current_timestamp);
warehouse_db$# return NEW;
warehouse_db$# end;
warehouse_db$# $first_trigger$ language plpgsql;
CREATE FUNCTION
warehouse_db=# create trigger audit_trigger
warehouse_db-# after insert on warehouse_tb1
warehouse_db-# for each row
warehouse_db-# execute procedure warehouse_audit_func();
CREATE TRIGGER
warehouse_db=# insert into warehouse_tb1 (warehouse_id ,warehouse_name,year_created,street_address,city,state,zip)values (3,'West point',2013,'Down Town','New London','CT',4321);
INSERT 0 1
warehouse_db=# select * from warehouse_audit ;
wlog_id | insertion_time
---------+-------------------------------
3 | 2015-10-14 10:49:05.819715+08
(1 row)

warehouse_db=# drop table warehouse_audit cascade;
DROP TABLE
warehouse_db=# create table warehouse_audit(wlog_id int not null,insertion_time text not null,operation_detail character varying);
CREATE TABLE

warehouse_db=# CREATE OR REPLACE FUNCTION warehouse_audit_func_all()
warehouse_db-# RETURNS trigger AS $BODY$
warehouse_db$# BEGIN
warehouse_db$# --this IF block confirms the operation type to be INSERT.
warehouse_db$# IF (TG_OP = 'INSERT') THEN
warehouse_db$# INSERT INTO warehouse_audit
warehouse_db$# (wlog_id, insertion_time, operation_detail)
warehouse_db$# VALUES
warehouse_db$# (new.warehouse_id, current_timestamp,'INSERT operation performed. Row
warehouse_db$# with id '||NEW.warehouse_id|| 'inserted');
warehouse_db$# RETURN NEW;
warehouse_db$# --this IF block confirms the operation type to be UPDATE.
warehouse_db$# ELSIF (TG_OP = 'UPDATE') THEN
warehouse_db$# INSERT INTO warehouse_audit
warehouse_db$# (wlog_id, insertion_time, operation_detail)
warehouse_db$# VALUES
warehouse_db$# (NEW.warehouse_id, current_timestamp,'UPDATE operation performed. Row
warehouse_db$# with id '||NEW.warehouse_id||' updates values '||OLD||' with '|| NEW.*
warehouse_db$# ||'.');
warehouse_db$# RETURN NEW;
warehouse_db$# --this IF block confirms the operation type to be DELETE
warehouse_db$# ELSIF (TG_OP = 'DELETE') THEN
warehouse_db$# INSERT INTO warehouse_audit
warehouse_db$# (wlog_id, insertion_time, operation_detail)
warehouse_db$# VALUES (OLD.warehouse_id, current_timestamp,'DELETE operation
warehouse_db$# performed. Row with id '||OLD.warehouse_id|| 'deleted ');
warehouse_db$# RETURN OLD;
warehouse_db$# END IF;
warehouse_db$# RETURN NULL;
warehouse_db$# END;
warehouse_db$# $BODY$ LANGUAGE plpgsql;
CREATE FUNCTION

warehouse_db=# create trigger audit_all_ops_trigger
warehouse_db-# after insert or update or delete on warehouse_tb1
warehouse_db-# for each row
warehouse_db-# execute procedure warehouse_audit_func_all();
CREATE TRIGGER

warehouse_db=# insert into warehouse_tb1 (warehouse_id,warehouse_name,year_created,street_address,city,state,zip)values (4,'North point',2011,'Down Town','Carson','LA',4324);
INSERT 0 1
warehouse_db=# insert into warehouse_tb1 (warehouse_id,warehouse_name,year_created,street_address,city,state,zip)values (5,'South point',2012,'Down Town','Avalon','LA',4325);
INSERT 0 1
warehouse_db=# update warehouse_tb1 set city = 'arcadia' where warehouse_id = '4';
UPDATE 1
warehouse_db=# delete from warehouse_tb1 where warehouse_id =4;
DELETE 1

postgres=# select wlog_id,insertion_time from warehouse_audit ;
wlog_id | insertion_time
---------+-------------------------------
4 | 2015-11-10 16:56:25.728301+08
5 | 2015-11-10 16:56:40.24055+08
4 | 2015-11-10 16:56:54.938282+08
4 | 2015-11-10 16:57:06.432421+08
(4 rows)

create triggers on views
warehouse_db=# create table tab_view(emp_id int not null,emp_name varchar(10),emp_city varchar(10));
CREATE TABLE
warehouse_db=# insert into tab_view values (1,'Adam','Chicago');
INSERT 0 1

warehouse_db=# insert into tab_view values (2,'John','Miami');
INSERT 0 1
warehouse_db=# insert into tab_view values (3,'Smith','Dallas');
INSERT 0 1
warehouse_db=#
warehouse_db=# create view view_select as select * from tab_view;
CREATE VIEW
warehouse_db=# create function triggerfunc_on_view() returns trigger as $$
warehouse_db$# begin
warehouse_db$# if (TG_OP = 'insert') then
warehouse_db$# insert into tab_view values (new.emp_id,new.emp_name,new.emp_city);
warehouse_db$# return new;
warehouse_db$# end if;
warehouse_db$# return null;
warehouse_db$# end;
warehouse_db$# $$language plpgsql;
CREATE FUNCTION
warehouse_db=#
warehouse_db=# create trigger trigger_on_view
warehouse_db-# instead of insert on view_select
warehouse_db-# for each row
warehouse_db-# execute procedure triggerfunc_on_view();
CREATE TRIGGER

warehouse_db=# select tgname from pg_trigger, pg_class where tgrelid=pg_class.oid and relname='warehouse_tb1';
-[ RECORD 1 ]------------------------
tgname | RI_ConstraintTrigger_a_90297
-[ RECORD 2 ]------------------------
tgname | RI_ConstraintTrigger_a_90298
-[ RECORD 3 ]------------------------
tgname | audit_trigger
-[ RECORD 4 ]------------------------
tgname | audit_all_ops_trigger

warehouse_db=# drop trigger audit_all_ops_trigger on warehouse_tb1;
DROP TRIGGER
warehouse_db=#
warehouse_db=# select tgname from pg_trigger, pg_class where tgrelid=pg_class.oid and relname='warehouse_tb1';
-[ RECORD 1 ]------------------------
tgname | RI_ConstraintTrigger_a_90297
-[ RECORD 2 ]------------------------
tgname | RI_ConstraintTrigger_a_90298
-[ RECORD 3 ]------------------------
tgname | audit_trigger

warehouse_db=# select current_user;
-[ RECORD 1 ]+---------
current_user | postgres

postgres=# select trigger_name from information_schema.triggers;
trigger_name
-----------------------
audit_trigger
audit_all_ops_trigger
audit_all_ops_trigger
audit_all_ops_trigger
(4 rows)

list the triggers you have created as follows:

postgres=# SELECT * FROM pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdefer
rable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual
---------+-----------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------
------+----------------+---------+--------+--------+--------
24576 | audit_all_ops_trigger | 24590 | 29 | O | f | 0 | 0 | 0 | f
| f | 0 | | x |
24595 | trigger_on_view | 24599 | 69 | O | f | 0 | 0 | 0 | f
| f | 0 | | x |
(2 rows)

see triggers associated with a particular table

postgres=# select tgname from pg_trigger ,pg_class where tgrelid=pg_class.oid and relname='warehouse_tbl';
tgname
-----------------------
audit_all_ops_trigger
(1 row)

postgres=# drop trigger audit_trigger on warehouse_tbl;
DROP TRIGGER
postgres=# drop trigger audit_all_ops_trigger on warehouse_tbl;
DROP TRIGGER
postgres=# drop trigger audit_all_ops_trigger on warehouse_tbl;