Triggers

 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'>     END;'
        test-> LANGUAGE 'plpgsql';
        CREATE 
         
        test=> CREATE TRIGGER trigger_statename
        test-> BEFORE INSERT OR UPDATE
        test-> ON statename
        test-> FOR EACH ROW
        test-> EXECUTE PROCEDURE trigger_insert_update_statename();
        CREATE 
         
        test=> DELETE FROM statename;
        DELETE 1
        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                       
        (1 row)
         

This function uses the new RECORD variable to perform the following actions:

• Reject a state code that is not exactly two alphabetic characters
• Reject a state name that contains nonalphabetic characters
• Reject a state name less than three characters in length
• Uppercase the state code
• Capitalize the state name

If a user enters invalid data, RAISE EXCEPTION  aborts the current query and displays an appropriate error message. Validity checks can also be performed using CHECK  constraints (see Section ). 

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.