❮ Postgresql Schema Postgresql Delete ❯

PostgreSQL Triggers

PostgreSQL triggers are database callback functions that are automatically executed or invoked when a specified database event occurs.

Here are some important points about PostgreSQL triggers:

PostgreSQL triggers can be fired in the following situations:

Before the operation (before checking constraints and attempting to insert, update, or delete).
After the operation (after checking constraints and completing the insert, update, or delete).
Update operation (when inserting, updating, or deleting in a view).

The FOR EACH ROW attribute of a trigger is optional. If selected, it is invoked once for each row modified by the operation. Conversely, if FOR EACH STATEMENT is selected, the trigger is executed once for each statement, regardless of how many rows are modified.

The WHEN clause and trigger actions can access each row element when referencing NEW.column-name and OLD.column-name during insert, delete, or update operations. Here, column-name is the name of the column in the table associated with the trigger.

If a WHEN clause exists, the PostgreSQL statement will only execute for rows where the WHEN clause is true. If no WHEN clause is present, the PostgreSQL statement will execute for every row.

The BEFORE or AFTER keywords determine when the trigger action is executed, specifying whether it should occur before or after the associated row's insert, update, or delete.

The table to be modified must exist in the same database as the table or view to which the trigger is attached, and must be referred to using only tablename, not database.tablename.

When creating a constraint trigger, the constraint option is specified. This is similar to regular triggers, but allows for adjusting the timing of the trigger's firing. When the constraint implemented by the constraint trigger is violated, it will throw an exception.

Syntax

The basic syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic....
];

Here, event_name can be INSERT, DELETE, and UPDATE database operations on the mentioned table table_name. You can optionally specify FOR EACH ROW after the table name.

The following is the syntax for creating a trigger on one or more specified columns of a table during an UPDATE operation:

CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic....
];

Example

Let's assume a scenario where we want to maintain an audit trail for each record inserted into a newly created COMPANY table (if it already exists, it will be dropped and recreated):

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

To maintain an audit trail, we will create a new table called AUDIT. Whenever a new record is inserted into the COMPANY table, a log message will be inserted into it:

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

Here, ID is the ID of the AUDIT record, EMP_ID is the ID from the COMPANY table, and DATE will hold the timestamp when the record in COMPANY was created. Now, let's create a trigger on the COMPANY table as follows:

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

auditlogfunc() is a PostgreSQL procedure defined as follows:

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;

Now, let's start inserting data into the COMPANY table: At this moment, a record is inserted into the COMPANY table:

Simultaneously, a record is also inserted into the AUDIT table because we created a trigger when inserting into the COMPANY table. Similarly, we can create triggers based on requirements for updates and deletions:

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Listing Triggers

You can list all triggers in the current database by querying the pg_trigger table:

tutorialprodb=# SELECT * FROM pg_trigger;

If you want to list triggers for a specific table, the syntax is as follows:

tutorialprodb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

The result is as follows:

tgname
-----------------
 example_trigger
(1 row)

Deleting a Trigger

The basic syntax for deleting a trigger is:

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

The command to delete the trigger named example_trigger on the company table is:

drop trigger example_trigger on company;

❮ Postgresql Schema Postgresql Delete ❯