Contents /
Previous /
Next
Trigger
Triggers are
special server-side functions that are called when a table is modified.
Also Rules allow SQL queries to be executed when a table is accessed.
Triggers and rules are implemented differently, however. Triggers call
server-side functions for each modified row, whereas rules rewrite
user queries or add queries.
Triggers offer a way to perform actions on
INSERT , UPDATE , or DELETE. for example, for checking or modifying
a column value before it is added to the database.
You cannot create SELECT triggers (SELECT does not modify any rows).
CREATE TRIGGER will enter a new trigger into the current data base:
<![CDATA[
CREATE TRIGGER name { BEFORE | AFTER } { event [OR ...] }
ON table FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE func ( arguments )
name : The name to give the new trigger.
table : The name of an existing table.
event : One of INSERT, DELETE or UPDATE.
func : A user-supplied function.
]]>
The trigger will be associated with the relation table and will
execute the specified function func.
The trigger can be specified to fire either BEFORE or AFTER the
operation is attempted on a tuple.
Implementation of a Trigger
Triggers have predefined
variables to access the row that caused the trigger. For INSERT
triggers, the variable new represents the row being inserted. For
DELETE , the variable old represents the row being deleted. For
UPDATE, triggers can access the pre-UPDATE row using old and the
post-UPDATE row using new. These variables are the same as the old and
new variables employed in rules.
Example:
<![CDATA[
INSERT INTO stud (id, name) VALUES (5, 'BoB') ;
SELECT * from stud ;
id | name | semester | diploma | gender
----+------+----------+---------+--------
2 | lisa | 2 | bio | f
5 | BoB | | bio | f
CREATE OR REPLACE FUNCTION lower_name()
RETURNS OPAQUE AS '
BEGIN
new.name = lower (new.name);
RETURN new;
END;'
LANGUAGE 'plpgsql' ;
CREATE TRIGGER lower_names AFTER INSERT
ON stud FOR EACH ROW
EXECUTE PROCEDURE lower_name() ;
INSERT INTO stud (id, name) VALUES (6, 'BoBy') ;
SELECT * from stud ;
id | name | semester | diploma | gender
----+------+----------+---------+--------
2 | lisa | 2 | bio | f
5 | BoB | | bio | f
6 | boby | | bio | f
]]>