Intro: PostgreSQL support triggers. Trigger is a special type of function which is executed BEFORE or AFTER database row in database table is inserted , updated or deleted.
2 main steps to crate and assign trigger to PostgreSQL table are:
1. Create trigger function
2. Assign trigger function to table and specify when to execute trigger (BEFORE or AFTER events INSERT, UPDATE, DELETE events)
In this example we will create 2 table my_user and my_city, create trigger function and assign it to table my_user. It will be executed on BEFORE INSERT and UPDATE events and will add city to table my_city if it not exists.
Preconditions:
1. pgAdmin III installed
2. PostgreSQL installed
Let's write some code :)
1. Open SQL editor in pgAdmin III
2. Write and execute SQL to create sequencers for our table's id fields:
-- Create sequence for tables id
CREATE SEQUENCE my_user_id_seq
START 1000;
CREATE SEQUENCE my_city_id_seq
START 1000;
3. Write and execute SQL to create tables:
-- Create tables, and set ID's default values our sequencers
CREATE TABLE my_user(
id
numeric DEFAULT nextval(
'my_user_id_seq')
PRIMARY KEY
, name
character varying(
255)
NOT NULL UNIQUE
, city
character varying(
255)
NOT NULL
);
CREATE TABLE my_city(
id
numeric DEFAULT nextval(
'my_city_id_seq')
PRIMARY KEY
, city
character varying(
255)
NOT NULL UNIQUE
);
4. Create and execute
trigger function
CREATE OR REPLACE FUNCTION func_my_user_city_trigger()
RETURNS trigger AS
$BODY$
DECLARE
-- declare varyiable
city_exist int;
BEGIN
-- TG_OP - is build-in PostgreSQL trigger variable with event type name
IF TG_OP=
'INSERT' OR TG_OP=
'UPDATE'
THEN
-- NEW - is build-in PostgreSQL trigger variable - it's table row with new data.
-- OLD - is build-in PostgreSQL trigger variable - it's table row with old data (for UPDATE event)
-- Please note:
-- in BEFORE events NEW row NOT equals OLD row (exept data is same).
-- in AFTER events NEW row equals OLD row.
IF NEW.city
IS NOT NULL THEN
-- Check if city already not exist
SELECT COUNT(*)
FROM my_city
AS c
WHERE c.city = NEW.city
INTO city_exist;
IF city_exist = 0
THEN
INSERT INTO my_city(city)
VALUES(NEW.city);
END IF;
END IF;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql
VOLATILE
COST 100;
5. Assign trigger function to table - specify BEFORE | AFTER INSERT | UPDATE | DELETE.
In our example we will use BEFORE INSERT | UPDATE.
CREATE TRIGGER my_user_city_trigger
BEFORE INSERT OR UPDATE
ON my_user
FOR EACH ROW
EXECUTE PROCEDURE func_my_user_city_trigger();
Hooray, that's all !!! :)
Now let's do some testing:
1. Write and execute following script:
DELETE FROM my_user;
DELETE FROM my_city;
INSERT INTO my_user(name,city)
VALUES(
'Obi-Wan' , 'Kiev' );
INSERT INTO my_user(name,city)
VALUES(
'Darth Vader' ,
'Moscow' );
INSERT INTO my_user(name,city)
VALUES(
'Anakin Skywalker' ,
'New York' );
UPDATE my_user
SET city =
'Moscow' WHERE name =
'Obi-Wan';
UPDATE my_user
SET city =
'Moscow' WHERE name =
'Darth Vader';
UPDATE my_user
SET city =
'Beijing' WHERE name =
'Anakin Skywalker';
2. Select all records from
my_city, result should be:
