Create Trigger in PostgreSQL

PostgreSQL

The trigger will be associated with the specified table, view, or foreign table and will execute the specified function function_name when certain events occur.

The 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); or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed); or instead of the operation (in the case of inserts, updates or deletes on a view). If the trigger fires before or instead of the event, the trigger can skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only). If the trigger fires after the event, all changes, including the effects of other triggers, are "visible" to the trigger.

Example target: when drop rows in client table, insert new record in uninstall table.

List of databases

test1=# \l
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges 
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | xuri  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | xuri  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/xuri          +
           |       |          |             |             | xuri=CTc/xuri
 template1 | xuri  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/xuri          +
           |       |          |             |             | xuri=CTc/xuri
 test1     | xuri  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

List of relations

test1=# \dt public.*
         List of relations
 Schema |   Name    | Type  | Owner 
--------+-----------+-------+-------
 public | client    | table | xuri
 public | uninstall | table | xuri
(2 rows)

List of client and uninstall schema

test1=# \d+ client
                                                  Table "public.client"
 Column |       Type        |                      Modifiers                      | Storage  | Stats target | Description 
--------+-------------------+-----------------------------------------------------+----------+--------------+-------------
 id     | integer           | not null default nextval('client_id_seq'::regclass) | plain    |              | 
 name   | character varying |                                                     | extended |              | 
Indexes:
    "client_pkey" PRIMARY KEY, btree (id)


test1=# \d+ uninstall
                                                  Table "public.uninstall"
 Column |       Type        |                       Modifiers                        | Storage  | Stats target | Description 
--------+-------------------+--------------------------------------------------------+----------+--------------+-------------
 id     | integer           | not null default nextval('uninstall_id_seq'::regclass) | plain    |              | 
 name   | character varying |                                                        | extended |              | 
Indexes:
    "uninstall_pkey" PRIMARY KEY, btree (id)

Install plpgsql to database

$ createlang plpgsql DATABASE

Create trigger process

CREATE OR REPLACE FUNCTION notify_trigger() RETURNS trigger AS $BODY$
BEGIN
    IF( TG_OP='DELETE' ) THEN
        INSERT INTO uninstall (id) VALUES(OLD.id, OLD.name);
    ELSE
    END IF;
    RETURN NULL;
END;

$BODY$
    LANGUAGE 'plpgsql';

Create trigger

CREATE TRIGGER notify_trigger
    AFTER DELETE
    ON client
    FOR EACH ROW
    EXECUTE PROCEDURE notify_trigger();

Reference PostgreSQL Documentation - CREATE TRIGGER

Create Trigger in PostgreSQL
9 votes, 4.78 avg. rating (95% score)