SQL Trigger – A complete Example in PostgreSQL

What is an SQL Trigger ?

Before going into the PostgreSQL trigger example, let’s first try to define what an sql trigger is.

A trigger is associated with a table or view and is fired whenever an event occur. The ‘events’ are INSERT, DELETE, UPDATE or TRUNCATE.

Trigger will eventually call a function which is written in PL/SQL or PL/pgSQL or any other compatible language.

Trigger can be set to fire BEFORE an event occur or AFTER an event occur or even we can bypass the event by using the INSTEAD OF command.

If we configured a trigger to fire BEFORE an INSERT or UPDATE, we will have the additional benefit of modifying the new data to be inserted or updated, and even skipping the operation itself!

There are both Row level trigger and Statement Level trigger available. A row level trigger is fired for each affected row. A statement level trigger is fired only once for a statement. To make it even simpler, lets consider the statement: UPDATE account_current SET balance = balance + 100 WHERE balance > 100000; Executing this statement may affect more than one rows. If a row level trigger is defined for the table, the trigger will be fired for each updated rows. But if it was a statement level trigger,it would have fired only once.

That is pretty much about the idea behind sql triggers. If you still need more theory, read here.

The use case

The sql trigger example given here assumes you have PostgreSQL installed in your system along with PL/pgSQL(which comes default in most distributions).  We will be creating three types of triggers which are supported by PostgreSQL :- sql update trigger, sql insert trigger and sql delete trigger.

Ok, let’s start with the use case. What we have in our DB is three tables: account_current, account_savings and log.

CREATE TABLE account_current
(
  customer_id integer NOT NULL,
  customer_name character varying,
  balance numeric,
  CONSTRAINT account_current_pkey PRIMARY KEY (customer_id)
)

 

CREATE TABLE account_savings
(
customer_id integer NOT NULL,
customer_name character varying,
balance numeric,
CONSTRAINT account_savings_pkey PRIMARY KEY (customer_id)
)

 

CREATE TABLE log
(
log_id serial NOT NULL,
log_time time with time zone,
description character varying,
CONSTRAINT log_pkey PRIMARY KEY (log_id)
)

The first two tables stores the customer name along with a unique id and their current or savings account balance respectively. You might be expecting one users table with user details alone, but for the sake of simplicity we ignore normalization here. The third table log is an auditing table which store each action or transaction with its time and a description.

The four kinds of actions possible here are:

1. A customer opens a savings and/or current account. This is done by an INSERT query.
2. A customer withdraw/ deposit money from/ to his/her account. Which is in effect an UPDATE query.
3. A customer sends or receive money to/from other customer. Which is in fact the combination of two UPDATE queries.
4. A customer closes his account, which is done by a DELETE command.

We need to keep track of all these actions. For that we are adding one log entry for each of the actions 1, 2 and 4. We wont be adding any separate entry for 3, since it can be divided into two update  entries.

So if Bob starts a savings account, deposit a 2000$ in it and then sends 300$ to Tom and finally decide to close the account and move to another bank, we will have the following entries in log table:

To populate the log table we are gonna use PostgreSQL triggers. We have to create two triggers, one for ‘savings table’ and one for ‘current table’. These two sql triggers will then hook in to one PL/pgSQL function. You might have noticed-we need only one single function. That means one function can be associated with n number of triggers!

So let’s start by writing the function first. We will create the trigger later on.

1. Writing an SQL Trigger Procedure(Function)

Before writing the procedure, let’s be familiar with some special variables created automatically by postgres to serve us.

NEW : Variable holding the new row to be inserted for INSERT/UPDATE command in row-level triggers. Its of data type RECORD.

OLD: Variable holding the old row for INSERT/UPDATE operation in row-level triggers. Data type is RECORD

TG_OP: Contains one of the strings of INSERT, UPDATE, DELETE, or TRUNCATE, tells for which operation the trigger is called.

TG_TABLE_NAME: The name of the table for which the trigger is fired.

TG_WHEN: A string of BEFORE, AFTER, or INSTEAD OF, depending on the trigger’s definition

There are more variables available. For a full list of variables, refer PostgreSQL Documentation.

An SQL trigger procedure must return either NULL or a record or row having the same structure of the table for which the it is fired. If a row-level trigger fired BEFORE returns a NULL, the rest of the operations including any other triggers and the actual INSERT/UPDATE/DELETE operation would be cancelled.

In the function definition, it must be written as it returns TRIGGER, no matter whether it return NULL or a RECORD/ROW.

So, in our trigger procedure we will be using some of these variables. The first thing we need to figure out in our trigger procedure is to determine for which table the trigger is fired for. So we begin with our procedure like this:

CREATE OR REPLACE FUNCTION add_log_trigg_function()
  RETURNS trigger AS
$BODY$
DECLARE
	account_type varchar;
BEGIN
	IF (TG_TABLE_NAME = 'account_current') THEN
		account_type := 'Current';
		RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;

	ELSIF (TG_TABLE_NAME = 'account_savings') THEN
		account_type := 'Savings';
		RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;

	END IF;

	RETURN null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION add_log_trigg_function()
  OWNER TO postgres;

Now we got the account type of the customer by checking the TG_TABLE_NAME variable. Next we are gonna handle the INSERT operation.

CREATE OR REPLACE FUNCTION add_log_trigg_function()
  RETURNS trigger AS
$BODY$
DECLARE
	account_type varchar;
BEGIN
	IF (TG_TABLE_NAME = 'account_current') THEN
		account_type := 'Current';
		RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;

	ELSIF (TG_TABLE_NAME = 'account_savings') THEN
		account_type := 'Savings';
		RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;

	END IF;

	IF (TG_OP = 'INSERT') THEN
		INSERT INTO log(
				log_time,
				description)
			VALUES(
				now(),
				'New customer added. Account type: ' || account_type || ', Customer ID: ' || NEW.customer_id || ', Name: ' || NEW.customer_name || ', Balance: ' || NEW.balance);
		RETURN NEW;
	END IF;

	RETURN null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Here we are checking the TG_OP variable to find whether an INSERT is operation is performed. If so it inserts a log entry in log table using a simple insert command. Notice that we are using the NEW variable to get the customer_id, name and balance of the new customer being inserted. The NEW variable is a special variable of type RECORD holding the new row to be inserted or updated. Think of it like a table with a single row of data. We can get the values of each column by NEW.column_name or even NEW.* for entire columns at once.

Also notice, if the IF condition is true then the function will return NEW. Otherwise it will return null, that means it will skip all further operations, as I said earlier. In the next step we are going to include the UPDATE and DELETE operations:

CREATE OR REPLACE FUNCTION add_log_trigg_function()
  RETURNS trigger AS
$BODY$
DECLARE
	account_type varchar;
BEGIN
	IF (TG_TABLE_NAME = 'account_current') THEN
		account_type := 'Current';
		RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;

	ELSIF (TG_TABLE_NAME = 'account_savings') THEN
		account_type := 'Savings';
		RAISE NOTICE 'TRIGER called on %', TG_TABLE_NAME;

	END IF;

	IF (TG_OP = 'INSERT') THEN
		INSERT INTO log(
				log_time,
				description)
			VALUES(
				now(),
				'New customer added. Account type: ' || account_type || ', Customer ID: ' || NEW.customer_id || ', Name: ' || NEW.customer_name || ', Balance: ' || NEW.balance);
		RETURN NEW;
	ELSIF (TG_OP = 'UPDATE') THEN
		IF (NEW.balance < 0) THEN
			RAISE EXCEPTION 'Can''t withdraw the amount because of low balance! Available balance: %, Requested amount: %', OLD.balance, OLD.balance + (- NEW.balance);
		END IF;
		IF NEW.balance != OLD.balance THEN
			EXECUTE 'INSERT INTO log(log_time,description) VALUES(now(), ''Balance updated. Account type: ' || account_type || ', Customer ID: '' || $1.customer_id || ''. Old balance: '' || $2.balance || '', New balance: '' || $1.balance)' USING NEW, OLD;
		END IF;
		RETURN NEW;

	ELSIF (TG_OP = 'DELETE') THEN
			INSERT INTO log(
				log_time,
				description)
			VALUES(
				now(),
				'Account deleted. Account type: ' || account_type || ', Customer ID: ' || OLD.customer_id);
			RETURN OLD;

	END IF;

	RETURN null;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Inside UPDATE’s IF condition we have two more IF conditions. First one to manage negative balance condition. If the NEW balance is found to be negative we will raise an Exception which will skip all further processes. In next IF condition we inserts the actual log entry. You can see here I have used dynamic command execution using EXECUTE command. Because some times you will need to use this technique. Be careful while adding quotes for literal values. Because here the query itself is written as a string and any further literal value inside the query needs secondary quoting with multiple single quotes(two single quotes, eg:  ”value”). We are passing the NEW and OLD variables via the USING expression, so that $1 hold NEW and $2 holds OLD.

Also you are not allowed to use $1 or $2 inside secondary quotes. If you do so, it will not substitute for NEW or OLD, instead it will be considered as literal strings ‘$1’ or ‘$2’. So we are using concatenation operator to make it work.

The DELETE block is pretty much straight forward.

2. Associating SQL Trigger Procedure to Table

So, we are done with our sql trigger procedure. Next, we are going to make the real trigger object which associates the trigger procedure to our account tables.

CREATE TRIGGER add_log_current_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON account_current
FOR EACH ROW
EXECUTE PROCEDURE add_log_trigg_function();

CREATE TRIGGER add_log_savings_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON account_savings
FOR EACH ROW
EXECUTE PROCEDURE add_log_trigg_function();

3. Testing our SQL Trigger Example

Now we can test our trigger by performing some operations.

INSERT INTO account_savings VALUES (1, 'Bob', 2000);
INSERT INTO account_savings VALUES (2, 'Tom', 1000);
INSERT INTO account_current VALUES (3, 'Roy', 12000);

UPDATE account_savings SET balance = balance - 300 WHERE customer_id = 1;
UPDATE account_savings SET balance = balance + 300 WHERE customer_id = 2;
DELETE FROM account_savings WHERE customer_id = 1;

Do a SELECT on log table to see the result.

So, we done with our simple sql trigger example. There is more about SQL triggers to learn. I hope this example will help you begin with SQL triggers in postgresql.

If you want the full SQL source dump of SQL trigger example along with sample data, download it here:

PostgreSQL_trigger_example.zip