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();