A Brief Real-world Trigger Example
Created 2003-03-13 by Richard Huxton (dev@archonet.com)
Version: First Draft - treat with caution
This is a real-world example, showing how you can use the plpgsql procedural
language to build a trigger function to enforce integrity beyond that which
foreign keys can offer you. The tables have been simplified to the minimum
required for this example, but represent real tables for a real project.
The Database
- We have a set of products, each of which has a certain type.
- We have a set of servers, each of which can only serve a specific type of product.
- Servers provide a subset of products and the same product can be available from many servers.
This gives us a table structure like:
product (pr_id, pr_type)
server (svr_id, pr_type)
server_products (svr_id, pr_id)
We can use "foreign keys":#references to make sure that 'server_products' have a valid
'svr_id' and 'pr_id' but if we want to enforce the type of a product we need
to check *two* tables, because what matters is that the 'pr_type' from
'product' matches the corresponding one in 'server'.
Solution 1 (in an ideal world...)
A simple solution would be to define a view 'possible_server_products' that
would contain 'svr_id','pr_type' and 'pr_id' and then reference that.
Unfortunately, PostgreSQL can't check a foreign key against a view, only
against a real table. This isn't a theoretical limitation of relational theory,
but there are some complex implementation issues, so it isn't likely to
happen any time soon.
I would describe how to implement foreign keys against views, but there
isn't space in the margin here ;-).
Solution 2 (well, it' not normal...)
If we change our definitions slightly, so we have 'server_products (svr_id,pr_type,pr_id)'
we can have a foreign key referencing '(svr_id,pr_type)' in table 'server' and
another on '(pr_type,pr_id)' in 'product' that does exactly what we want.
Unfortunately, we now need to look up the 'pr_type' in our application when
we insert a new product. We can avoid that by defining a view that looked like
our original version of 'server_products' and write rules that do the lookup for us.
There is however, a more fundamental problem with this solution - we have a
redundant 'pr_type' in every row of 'server_products'. Is it part of the primary
key for this table, or if not does it depend on the primary key? Well, our
primary key is clearly '(svr_id,pr_id)' since this identifies the row. But -
'pr_type' doesn't depend on this key, it depends on 'svr_id' alone (or 'pr_id'
alone, depending on how you want to look at it). This is a violation of 2nd
Normal Form ("2NF":#references) and I like a normalised database, so this
solution isn't acceptable.
Solution 3 (here's one I made earlier...)
So - we don't want to change our table definitions but do want to enforce
product type. To do this we will need to manually add three triggers (one for
each table involved) and a function or functions to enforce our constraints.
In this case, I chose to have one function used by all three triggers. You
could make a good argument for three different functions, but having all the
code in one place makes it less likely I'll forget to change something if I
change the database structure.
Assuming we've run the "createlang":#references utility, we'll define our function using:
CREATE OR REPLACE FUNCTION check_srv_prodtype() RETURNS trigger AS '
...code here...
' LANGUAGE 'plpgsql';
Within the function we'll need to check the value of the 'TG_RELNAME'
pseudo-variable which tells us which table triggered a call to us. Then, we can
check the contents of the 'NEW' pseudo-record to see if the values are
acceptable. If they are, we return 'NEW' otherwise we return 'NULL'. The code
fragment to check changes to server_products would be something like:
IF TG_RELNAME=''server_products'' THEN
SELECT pr_type INTO prod_type FROM possible_server_products WHERE svr_id=NEW.svr_id AND pr_id=NEW.pr_id;
IF FOUND THEN
RETURN NEW;
ELSE
RETURN NULL;
END IF;
ELSE...
-- Definition of possible_server_products is:
CREATE VIEW possible_server_products AS
SELECT s.svr_id, s.pr_type, p.pr_id FROM server s, product p WHERE p.pr_type=s.pr_type;
In the actual function we'll want to generate an error message as well as
returning NULL and adding some comments. Then, we can set up triggers to call
our function.
CREATE TRIGGER check_server_products
AFTER INSERT OR UPDATE ON server_products
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();
This tells PG to call our function every time an insert or update is made to
table server_products after other checks but before the transaction is
committed. If we said BEFORE INSERT... the function would be called before any
foreign key checks were run.
The full listing to reproduce this solution is at the end of this document.
Problems with Solution 3
No system is perfect. There are two main problems with the solution below.
Firstly, the function needs to be run for every row inserted or modified in
all three tables, and it runs queries for each test. If you have a lot of
frequently modified rows this is going to be a performance hit. It might be
an option to write the function in 'C' but since the function is so simple,
gains would probably be small.
Secondly, the function itself might have an error. Since we couldn't meet our
integrity requirements with built-in features we had no choice but to write some
code, but it should be tested. As an example, in the first draft of this function
I forgot to test changes to the 'server' table and only caught this when testing.
References
"Foreign keys":http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createtable.html reference on the CREATE TABLE page.
The "createlang":http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=app-createlang.html utility.
"CREATE TRIGGER":http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createtrigger.html reference.
"Trigger Functions":http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html in plpgsql.
An overview of the "Normal Forms":http://home.earthlink.net/~billkent/Doc/simple5.htm or as a "pdf":http://mingo.info-science.uiowa.edu/courses/automation/reference/p120-kent.pdf.
SQL Script
The SQL to recreate this example is given below - you should cut and paste it
into a text editor and save it as 'briefex_trigger.txt'. You can then run it
from psql with '\i briefex_trigger.txt'. It has been tested on PG v7.3 so if
you have problems please make sure no oddities have crept in during cut & paste.
DROP TABLE product CASCADE;
DROP TABLE server CASCADE;
DROP VIEW possible_server_products CASCADE;
DROP VIEW actual_server_products CASCADE;
DROP TABLE server_products CASCADE;
CREATE TABLE product (
pr_id int NOT NULL,
pr_type int NOT NULL,
PRIMARY KEY (pr_id)
);
CREATE TABLE server (
svr_id varchar(4) NOT NULL,
pr_type int NOT NULL,
PRIMARY KEY (svr_id)
);
CREATE TABLE server_products (
svr_id varchar(4) NOT NULL REFERENCES server (svr_id),
pr_id int NOT NULL REFERENCES product (pr_id),
PRIMARY KEY (svr_id, pr_id)
);
-- Now add some triggers to check pr_type is valid for servers
--
CREATE VIEW possible_server_products AS
SELECT s.svr_id, s.pr_type, p.pr_id FROM server s, product p WHERE p.pr_type=s.pr_type;
CREATE VIEW actual_server_products AS
SELECT s.svr_id, s.pr_type, sp.pr_id FROM server s, server_products sp WHERE s.svr_id=sp.svr_id;
CREATE OR REPLACE FUNCTION check_srv_prodtype() RETURNS trigger AS '
DECLARE
prod_id int4;
prod_type int4;
server_id varchar(4);
BEGIN
IF TG_RELNAME=''server_products'' THEN
-- check product can be allocated to this server
SELECT pr_type INTO prod_type FROM possible_server_products WHERE svr_id=NEW.svr_id AND pr_id=NEW.pr_id;
IF FOUND THEN
-- product type is valid for this server
RETURN NEW;
ELSE
SELECT pr_type INTO prod_type FROM product WHERE pr_id=NEW.pr_id;
IF FOUND THEN
RAISE EXCEPTION ''Server % does not support products of type % (product id = %)''
, NEW.svr_id, prod_type, NEW.pr_id;
ELSE
-- Need this in case we are called from BEFORE trigger
-- in which case foreign key check has not happened
RAISE EXCEPTION ''Server % does not support non-existent products (product id = %)''
, NEW.svr_id, NEW.pr_id;
END IF;
RETURN NULL;
END IF;
ELSIF TG_RELNAME=''product'' THEN
-- Inserting/updating a "product"
SELECT svr_id INTO server_id FROM actual_server_products WHERE pr_id=NEW.pr_id AND pr_type<>NEW.pr_type;
IF NOT(FOUND) THEN
-- this product is either not used or the new type is valid where it is used.
RETURN NEW;
ELSE
SELECT pr_type INTO prod_type FROM server WHERE svr_id=server_id;
RAISE EXCEPTION ''Server % uses product % and only allows product type %''
, server_id, NEW.pr_id, prod_type;
RETURN NULL;
END IF;
ELSE
-- Must be updating a "server", see if there are any products for it.
SELECT pr_id INTO prod_id FROM server_products WHERE svr_id=NEW.svr_id;
IF found THEN
-- Have products, so no change to pr_type allowed.
IF OLD.pr_type<>NEW.pr_type THEN
SELECT pr_type INTO prod_type FROM product WHERE pr_id=prod_id;
RAISE EXCEPTION ''Server % uses product % and so requires product type %''
, NEW.svr_id, prod_id, prod_type;
RETURN NULL;
END IF;
END IF;
-- All OK, either no server_products or type isnt changed
RETURN NEW;
END IF;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER check_server_products
AFTER INSERT OR UPDATE ON server_products
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();
CREATE TRIGGER check_used_product_type
AFTER INSERT OR UPDATE ON product
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();
CREATE TRIGGER check_server_product_type
AFTER INSERT OR UPDATE ON server
FOR EACH ROW EXECUTE PROCEDURE check_srv_prodtype();
-- OK, now try inserting some data
INSERT INTO product VALUES (1,1);
INSERT INTO product VALUES (2,2);
INSERT INTO product VALUES (3,1);
INSERT INTO product VALUES (4,2);
INSERT INTO server VALUES ('a',1);
INSERT INTO server VALUES ('b',2);
INSERT INTO server_products VALUES ('a',1);
INSERT INTO server_products VALUES ('a',3);
-- Next insert should fail
INSERT INTO server_products VALUES ('a',2);
-- And this should fail
UPDATE product SET pr_type=2 wHERE pr_id=1;
-- As should this
UPDATE server SET pr_type=2 WHERE svr_id='a';