PostgreSQL Triggers

Creating a Trigger in PostgreSQL

• PostgreSQL Triggers are database call-back functions, which are automatically performed/invoked when a specified database event occurs.
• The following are important points about PostgreSQL triggers −
• PostgreSQL trigger can be specified to fire
• Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted)
• After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed)
• A trigger that is marked FOR EACH ROW is called once for every row that the operation modifies. In contrast, a trigger that is marked FOR EACH STATEMENT only executes once for any given operation, regardless of how many rows it modifies.
• If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name.
• The BEFORE, AFTER or INSTEAD OF keyword determines when the trigger actions will be executed relative to the insertion, modification or removal of the associated row.
• Triggers are automatically dropped when the table that they are associated with is dropped.

Example:

CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE AUDIT(
EMP_ID INT NOT NULL,
ENTRY_DATE TEXT NOT NULL
);

Function:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
BEGIN
INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql

Trigger:

CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

Views:

SELECT * FROM pg_trigger;

Drop:

DROP TRIGGER trigger_name;