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

ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO warehouse_audit
(wlog_id, insertion_time, operation_detail)
VALUES (OLD.warehouse_id, current_timestamp,'DELETE
operation performed. Row with id '||OLD.warehouse_id||
'deleted ');
RETURN OLD;
END IF;
RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;
Now, we will create the trigger and bind it to the warehouse_tbl table as follows:
warehouse_db=# CREATE TRIGGER audit_all_ops_trigger
AFTER INSERT OR UPDATE OR DELETE ON warehouse_tbl
FOR EACH ROW
EXECUTE PROCEDURE warehouse_audit_func_all();
Let's test our trigger by inserting, updating, and deleting rows from the
warehouse_tbl table.
Insert rows in the warehouse_tbl table as follows:
warehouse_db=# INSERT INTO warehouse_tbl
(warehouse_id, warehouse_name, year_created, street_address,
city, state, zip)
VALUES
(4, 'North point', 2011, 'Down Town', 'Carson', 'LA', 4324);
warehouse_db=# INSERT INTO warehouse_tbl
(warehouse_id, warehouse_name, year_created, street_address,
city, state, zip)
VALUES
(5, 'South point', 2012, 'Down Town', 'Avalon', 'LA', 4325);
Update the city row in the warehouse_tbl table as follows:
warehouse_db=# UPDATE warehouse_tbl set city = 'arcadia' WHERE
warehouse_id = '4';
Delete the row with warehouse_id as 4 in the warehouse_tbl table as follows:
warehouse_db=# DELETE FROM warehouse_tbl WHERE warehouse_id=4;