PostgreSQL Triggers

Creating a Trigger in PostgreSQL

• PostgreSQL Triggers are database callback 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;