Why use triggers?After some time using databases, you will encounter custom functions and triggers. Triggers are used to add automated function calls before or after an event. Triggers may be used for data cleaning, logging, integrity checks, and preventing invalid operations. In this post, I'll show a quick demonstration on how triggers are used in PostgreSQL and how you can take advantage of this feature.
CREATE OR REPLACE FUNCTION example_trigger_function() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'Hello there'; RETURN NEW; END; $$ LANGUAGE plpgsql;
To define a trigger, a trigger function is first created. The trigger function created is then bound to a table using CREATE TRIGGER. You can see a simple example below on how a trigger is bound to the insert event.
CREATE TRIGGER example_trigger AFTER INSERT ON products FOR EACH ROW EXECUTE PROCEDURE example_trigger_function();
Now, you'll see a notice after each insert operation on the products table.
INSERT INTO products(id, name, total, available) VALUES(100, 'Some Product', 400, 400); NOTICE: Hello there INSERT 0 1
Triggers in Action: Products & PurchasesWe'll be working on two tables: products and purchases. The products table will serve as the container of the items to be purchased while the the purchases table will contain all transactions. Each product will have 3 (integer) counter fields: total, available, and purchased. The total count is simply the sum of the available and purchased fields.
CREATE TABLE products( id integer PRIMARY KEY, name text NOT NULL, total integer NOT NULL DEFAULT 0, available integer NOT NULL DEFAULT 0, purchased integer NOT NULL DEFAULT 0, CHECK (total >= 0), CHECK (available >= 0), CHECK (purchased >= 0), CHECK (available <= total), CHECK (purchased <= total) ); INSERT INTO products(id, name, total, available) VALUES(1, 'Donut', 500, 500); INSERT INTO products(id, name, total, available) VALUES(2, 'Burger', 400, 400);
After creating the products table, we prepare the purchases table. Each purchase transaction is simply described by a Product ID and a value (stock count purchased).
CREATE TABLE purchases( id integer PRIMARY KEY, product_id integer REFERENCES products, value integer NOT NULL DEFAULT 0, CHECK (value > 0); )
Take note that the sum of the values per product in the purchases table must match the purchased count in the corresponding product entry in the products table. Here's where we can use triggers to automatically update the counts of the products table everytime a new record is inserted to the purchases table.
We first create a trigger function that updates the affected product row in the products table. Take note that we're just wrapping the update operation inside a trigger function below. After that, we bind the trigger function we created to the insert event on the purchases table.
CREATE OR REPLACE FUNCTION update_counts () RETURNS TRIGGER AS $$ BEGIN UPDATE products SET available = total - purchased - NEW.value, purchased = purchased + NEW.value WHERE id = NEW.product_id; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_counts_trigger AFTER INSERT ON purchases FOR EACH ROW EXECUTE PROCEDURE update_counts();
Now let's try to populate the purchases table. We'll be purchasing 25 donuts and 18 burgers.
INSERT INTO purchases(id, product_id, value) VALUES(1, 1, 5); INSERT INTO purchases(id, product_id, value) VALUES(2, 1, 10); INSERT INTO purchases(id, product_id, value) VALUES(3, 1, 10); INSERT INTO purchases(id, product_id, value) VALUES(4, 2, 8); INSERT INTO purchases(id, product_id, value) VALUES(5, 2, 10);
See how the counts in the products table are automatically updated. Take note that this is just a simple example and modifications are necessary before this can be used in production environments (e.g. transactions and locks).
SELECT * FROM products; id | name | total | available | purchased -----+--------------+-------+-----------+----------- 1 | Donut | 500 | 475 | 25 2 | Burger | 400 | 382 | 18
Pretty cool huh? We've also added CHECK constraints in the products table to prevent the purchased count from exceeding the total count.
Preventing DeletionsWe can also use triggers to prevent deletion of rows on an existing table. We simply create a trigger function that returns NULL and bind that trigger function before the delete event. Take note that this will not protect your table from being truncated with the TRUNCATE operation.
CREATE OR REPLACE FUNCTION prevent_deletion() RETURNS TRIGGER AS $$ BEGIN RAISE NOTICE 'DELETION NOT ALLOWED'; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER prevent_deletion_trigger BEFORE DELETE ON purchases FOR EACH ROW EXECUTE PROCEDURE prevent_deletion();
Let's try deleting one purchase entry in the purchases table. A notice saying "DELETION NOT ALLOWED" will be shown whenever an attempt to delete a row is made.
DELETE FROM purchases WHERE ID = 1; NOTICE: DELETION NOT ALLOWED DELETE 0
Word of AdviceTriggers are useful in certain actions such as integrity checks, logging, and applying custom constraints. Triggers should be used cautiously since they can lead to problems which are hard to debug. For more information on triggers, click here.