Deividson on Databases: Triggers

By Deividson Luiz Okopnik

Triggers

Triggers are pieces of code executed automatically when a certain action happens. That action can be any kind of data manipulation (insertion, update, or deletion). It can also be executed before or after the actual data manipulation, having different options and uses (validating data, allowing or disallowing data manipulations, changing other data, etc.).

In PostgreSQL, triggers are special stored procedures - so everything we saw on the last article can be used here, too. Let's go down to an example:

Example 1: Hit Counting

This can be used on a web page, or something similar. We will have a table to store the ID and IP of each access (you can store any information you want here, such as referrer, time and date, etc.), and another table with a single row to store the actual page views (this can be extended to have an ID for each page on your site, storing individual hits.) Here is the SQL to create these tables:

CREATE TABLE access
(
  access_id serial NOT NULL PRIMARY KEY,
  access_ip text
);

CREATE TABLE hit
(
  hit_id serial NOT NULL PRIMARY KEY,
  hit_value integer
);
insert into hit values(0, 0);

We'll want to increment the "hit_value" of ID 0 every time an access is recorded. We could use a simple "select count()" to count accesses, but that would mean losing the count when you do a clean up on the access table. (We will not want all that data there forever, will we?) To do it right, first we need to create a procedure that increments the "hit" table when "hit_id" = 0. This is the SQL to create this procedure:

create or replace function add_hit()
returns TRIGGER as $$
begin
	if(TG_OP='INSERT') then
		update hit set hit_value = (select hit_value from hit where hit_id = 0) +1 where hit_id = 0;
	end if;
	return new;
end; 
$$ Language PLPGSQL;

Here, we see three new commands/features in addition to what we used in the last article in this series: the first one is "returns TRIGGER as $$". This is a trigger-specific return type to hold the changed data that will be stored/updated/deleted from the database, useful when you need to add or change the data before inserting into the database. The other new command is "if(TG_OP='INSERT')". TG_OP will store the operation being executed in the database - useful when you use the same trigger on more than one event (insert/update/delete). And finally, we have "return new". "New" is an internal variable that stores the data after the changes. (In an insert, new is the data being inserted; on an update, new is the existing data after the update; on a delete, new does not exist.) Along with "new", there is also "old", which stores the data before the changes: on delete, old is the data that will be deleted; on an update, old is the data that will be changed, before the change; on an insert, old does not exist.

Now, we will turn our stored procedure into a trigger and activate it. Here is the SQL to do that:

create TRIGGER tg_add_hit before insert on access for each row execute procedure add_hit();

The syntax is pretty simple - "create TRIGGER <trigger name> <before/after> <event(s)> for each <row/statement> execute procedure <procedure name>([parameters])". trigger name is a unique name to identify the trigger, before/after defines if the procedure will be executed before or after the actual data change, events are the events when the trigger will be executed - 'insert', 'update', 'delete', or a mix of them ("on insert or update"). for each row means that the trigger will be executed for each row of data that gets changed, while the for each statement means it will only be executed once, no matter how many rows a single statement modifies. In the end, there's the procedure name and its parameters, if it takes any.

Now, to test this trigger, we'll run "select * from hit" to check the current count (should be 0). Then, insert an access with "insert into access(access_ip) values('111');". Then, do a "select * from hit" again, and you will notice that the count changed.

Example 2: Stock/Inventory Control

A classic use of triggers is stock/inventory control - keeping a record of how many of each product you have in stock, and using triggers to change the number of remaining items when some are sold. We will use the following tables in this example:

create table product(
pro_id serial primary key,
pro_name varchar(50),
pro_quant integer);

create table sale(
sale_id serial primary key,
sale_value date default current_date);

create table sale_product(
sp_id serial primary key, 
sale_id integer references sale(sale_id),
pro_id integer references product(pro_id), 
sp_quant integer);

insert into product(pro_name, pro_quant) values ('Computer', 10);
insert into product(pro_name, pro_quant) values ('Printer', 15);
insert into product(pro_name, pro_quant) values ('Monitor', 10);
insert into sale(sale_id) values (0);

Pretty simple - although I left some "details" (prices, clients, etc.) out of it so we could focus on the quantities and on our trigger. I've also created some basic test data for the products and sales tables. Now, let's create the stored procedure to remove products when they are sold, and activate the trigger for it. Here's the SQL to do that:

create or replace function upd_stock()
returns TRIGGER as $$
begin
	if((TG_OP='DELETE') OR (TG_OP='UPDATE')) then
	  update product set pro_quant = pro_quant + OLD.sp_quant where pro_id = OLD.pro_id;
	end if;
	if((TG_OP='UPDATE') OR (TG_OP='INSERT')) then
  	  update product set pro_quant = pro_quant - NEW.sp_quant where pro_id = NEW.pro_id;
	end if;

	if(TG_OP='DELETE') then
		return old;
	else
		return new;
	end if;
end; 
$$ Language PLPGSQL;

create TRIGGER tg_upd_stock before insert or update or delete on sale_product for each row execute procedure upd_stock();

OK, this one is a bit more complex, so let's go through it slowly. First, it's a trigger that runs on every event ("on insert or update or delete"). If the user is deleting data, it will only give the amount sold back to the stock. If it's an insert, then it will remove only the products being sold from the stock. Finally, if it's updating (changing), then the trigger will first add the old amount back into the product table, then it will remove the new quantity. This is done to prevent data corruption. Even if your system does not support data deletion, for example, this ensures that your database will remain correct, no matter what happens.

Now, if you do want to practice stored procedures and triggers, there are two additions you need to make to this last example. The first one will add a table to store data when you buy stuff and a trigger to add the products to the stock; the second one will add a total to the sales table, add the price of the product to the products table and the price of the product when it was sold to the sale_product table, and create a trigger to add the price of the sold products to the sale total.

Conclusion

PostgreSQL is a very advanced database system, and some of its features can aid you greatly in developing systems, eliminate the need for a considerable amount of external code, and usually result in a faster solution, reduced bandwidth requirements, etc. The options we saw in this series of articles are very powerful but are usually under-used - so it's good to remember that they exist. Who knows - next time you are developing something, they might be exactly what you need.

I hope you enjoyed these articles. In case of any questions or suggestions, make sure to send a Talkback message by clicking the link below.

Talkback: Discuss this article with The Answer Gang

---

Deividson was born in União da Vitória, PR, Brazil, on 14/04/1984. He became interested in computing when he was still a kid, and started to code when he was 12 years old. He is a graduate in Information Systems and is finishing his specialization in Networks and Web Development. He codes in several languages, including C/C++/C#, PHP, Visual Basic, Object Pascal and others.

Deividson works in Porto União's Town Hall as a Computer Technician, and specializes in Web and Desktop system development, and Database/Network Maintenance.