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

The preceding example is a good and easy one to start writing triggers but from a
database design perspective, you should be aware that the user can perform UPDATE
or DELETE operation as well. Therefore, the next example will teach you how to
handle the INSERT , UPDATE , and DELETE operations in one trigger function.
Let's modify the warehouse_audit table and add another column named
operation_detail . This column will store values for the respective operation
INSERT , UPDATE , or DELETE .
Let's empty the warehouse_audit table irst using the following statement:
warehouse_db=# DROP TABLE warehouse_audit CASCADE;
Create the warehouse_audit table again in the following manner:
warehouse_db=# CREATE TABLE warehouse_audit
(
wlog_id INT NOT NULL,
insertion_time TEXT NOT NULL,
operation_detail CHARACTER VARYING
);
Now create a trigger function as follows:
warehouse_db=# CREATE OR REPLACE FUNCTION
warehouse_audit_func_all()
RETURNS trigger AS $BODY$
BEGIN
--this IF block confirms the operation type to be INSERT.
IF (TG_OP = 'INSERT') THEN
INSERT INTO warehouse_audit
(wlog_id, insertion_time, operation_detail)
VALUES
(new.warehouse_id, current_timestamp,'INSERT
operation performed. Row with id '||NEW.warehouse_id||
'inserted');
RETURN NEW;
--this IF block confirms the operation type to be UPDATE.
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO warehouse_audit
(wlog_id, insertion_time, operation_detail)
VALUES
(NEW.warehouse_id, current_timestamp,'UPDATE operation
performed. Row with id '||NEW.warehouse_id||' updates
values '||OLD||' with '|| NEW.* ||'.');
RETURN NEW;
--this IF block confirms the operation type to be DELETE