While it is relatively easy to perform trigger actions conditionally inside the PL/pgSQL trigger function, it is often more efficient to skip invoking the trigger altogether. The performance effects of firing a trigger are not generally noticed when only a few events are fired. However, if you are bulk loading data or updating large portions of your table, the cumulative effects can certainly be felt. To avoid the overhead, it's best to call the trigger function only when it is actually needed.
There are two ways to narrow down when a trigger will be called in the CREATE TRIGGER command itself.
So, use the same syntax once more but with all the options this time:
CREATE TRIGGER name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR event ... ] }
[ OF column_name [ OR column_name ... ] ] ON table_name
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )You can use the
WHEN clause to only fire a trigger based on a condition (such as a certain time of the day) or only when certain fields are updated. We will now take a look at a couple of examples.
A flexible way to control triggers is to use a generic WHEN clause that is similar to WHERE in SQL queries. With a WHEN clause, you can write any expression, except a subquery, that is tested before the trigger function is called. The expression must result in a Boolean value, and if the value is FALSE (or NULL, which is automatically converted to FALSE), the trigger function is not called.
For example, you can use this to enforce a "No updates on Friday afternoon" policy:
CREATE OR REPLACE FUNCTION cancel_with_message()
RETURNS TRIGGER AS $$
BEGIN
RAISE EXCEPTION '%', TG_ARGV[0];
RETURN NULL;
END;
$$ LANGUAGE plpgsql;This code just raises an exception with the string passed as an argument in the CREATE TRIGGER statement. Notice that we cannot use TG_ARGV[0] directly as the message because the PL/pgSQL syntax requires a string constant as the third element of RAISE.
Using the previous trigger function, you can set up triggers in order to enforce various constraints by specifying both the condition (in the WHEN(...) clause) and the message to be raised if this condition is met as the argument to the trigger function:
CREATE TABLE new_tasks(id SERIAL PRIMARY KEY, sample TEXT);
CREATE TRIGGER no_updates_on_friday_afternoon
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON new_tasks
FOR EACH STATEMENT
WHEN (CURRENT_TIME > '12:00' AND extract(DOW from CURRENT_TIMESTAMP) = 5)
EXECUTE PROCEDURE cancel_with_message('Sorry, we have a "No task change on Friday afternoon" policy!'),Now, if anybody tries to modify the new_tasks table on a Friday afternoon, they get a message about this policy, as shown here:
postgres=# insert into new_tasks(sample) values("test");
ERROR: Sorry, we have a "No task change on Friday afternoon" policy!Note
One thing to note about trigger arguments is that the argument list is always an array of text (text[]).
All of the arguments given in the CREATE TRIGGER statement are converted to strings, and this includes any NULL values.
This means that putting NULL in the argument list results in the text NULL in the corresponding slot in TG_ARGV.
Another way to control when a trigger is fired is using a list of columns. In the UPDATE triggers, you can specify one or more comma-separated columns to tell PostgreSQL that the trigger function should only be executed if any of the listed columns change.
It is possible to construct the same conditional expression with a WHEN clause, but the list of columns has cleaner syntax:
WHEN(
NEW.column1 IS DISTINCT FROM OLD.column1
OR
NEW.column2 IS DISTINCT FROM OLD.column2)A common example of how this conditional expression is used is raising an error each time someone tries to change a primary key column. The IS DISTINCT FROM function makes sure that the trigger is only executed when the new value of column1 is different from the old value. This can be easily done by declaring an AFTER trigger using the cancel_op() trigger function (defined previously in this chapter), as follows:
CREATE TRIGGER disallow_pk_change AFTER UPDATE OF id ON table_with_pk_id FOR EACH ROW EXECUTE PROCEDURE cancel_op(); postgres=# INSERT INTO new_tasks DEFAULT VALUES; INSERT 0 1 packt=# SELECT * FROM new_tasks ; id | sample ----+-------- 1 | (1 row) packt=# UPDATE new_tasks SET id=0 where id=1; ERROR: YOU ARE NOT ALLOWED TO UPDATE ROWS IN public.new_tasks