A Brief Real-world Trigger Example

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 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) 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 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 reference on the CREATE TABLE page.

The createlang utility.

CREATE TRIGGER reference.

Trigger Functions in plpgsql.

An overview of the Normal Forms or as a 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.

NOTE - there seems to be an issue with some of the quotes in the listing below. All quotes in the function should be doubled up, any single quotes are an error.

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';