Rules allow SQL queries to be executed when a table is accessed (see Section ). Triggers offer an alternative way to perform actions on INSERT , UPDATE , or DELETE . They are ideal for checking or modifying a column value before it is added to the database.
Triggers and rules are implemented differently, however. Triggers call server-side functions for each modified row, whereas rules rewrite user queries or add queries. The former are ideal for checking or modifying a row before it is added to the database. The latter are ideal when the action affects other tables.
With triggers, special server-side functions can be called every time a row is modified. These special functions can be written in any server-side language except SQL. They control the action taken by the query. For example, they can reject certain values or modify values before they are added to the database. Triggers that return NULL cause the operation that caused the trigger to be ignored.
Server-side trigger functions are special because they 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.
Figure shows the creation of a special server-side
trigger function called trigger_insert_update_statename.
test=> CREATE FUNCTION trigger_insert_update_statename()
test-> RETURNS opaque
test-> AS 'BEGIN
test'> IF new.code !~ ''^[A-Za-z][A-Za-z]$''
test'> THEN RAISE EXCEPTION ''State code must be two alphabetic characters.'';
test'> END IF;
test'> IF new.name !~ ''^[A-Za-z ]*$''
test'> THEN RAISE EXCEPTION ''State name must be only alphabetic characters.'';
test'> END IF;
test'> IF length(trim(new.name)) < 3
test'> THEN RAISE EXCEPTION ''State name must longer than two characters.'';
test'> END IF;
test'> new.code = upper(new.code); -- uppercase statename.code
test'> new.name = initcap(new.name); -- capitalize statename.name
test'> RETURN new;
test-> LANGUAGE 'plpgsql';
test=> CREATE TRIGGER trigger_statename
test-> BEFORE INSERT OR UPDATE
test-> ON statename
test-> FOR EACH ROW
test-> EXECUTE PROCEDURE trigger_insert_update_statename();
test=> DELETE FROM statename;
test=> INSERT INTO statename VALUES ('a', 'alabama');
ERROR: State code must be two alphabetic characters.
test=> INSERT INTO statename VALUES ('al', 'alabama2');
ERROR: State name must be only alphabetic characters.
test=> INSERT INTO statename VALUES ('al', 'al');
ERROR: State name must longer than two characters.
test=> INSERT INTO statename VALUES ('al', 'alabama');
INSERT 292898 1
test=> SELECT * FROM statename;
code | name
AL | Alabama
This function uses the new RECORD variable to perform the following actions:
Uppercase and capitalization occur by simply assigning values to the new variable. The function return type is opaque because new is returned by the function.
CREATE TRIGGER causes trigger_insert_update_statename() to be called every time a row is inserted or updated in statename. The remaining queries in Figure show three rejected INSERTs as well as a successful INSERT that is properly uppercased and capitalized by the function.
Trigger functions can be quite complicated. They can perform loops, SQL queries, and any operation supported in server-side functions. See the CREATE_TRIGGER and DROP_TRIGGER manual pages for additional information.