postgresql Triggers and Trigger Functions Basic PL/pgSQL Trigger Function
Example
This is a simple trigger function.
CREATE OR REPLACE FUNCTION my_simple_trigger_function()
RETURNS trigger AS
$BODY$
BEGIN
-- TG_TABLE_NAME :name of the table that caused the trigger invocation
IF (TG_TABLE_NAME = 'users') THEN
--TG_OP : operation the trigger was fired
IF (TG_OP = 'INSERT') THEN
--NEW.id is holding the new database row value (in here id is the id column in users table)
--NEW will return null for DELETE operations
INSERT INTO log_table (date_and_time, description) VALUES (now(), 'New user inserted. User ID: '|| NEW.id);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
--OLD.id is holding the old database row value (in here id is the id column in users table)
--OLD will return null for INSERT operations
INSERT INTO log_table (date_and_time, description) VALUES (now(), 'User deleted.. User ID: ' || OLD.id);
RETURN OLD;
END IF;
RETURN null;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Adding this trigger function to the users table
CREATE TRIGGER my_trigger
AFTER INSERT OR DELETE
ON users
FOR EACH ROW
EXECUTE PROCEDURE my_simple_trigger_function();
