VII. SQL Stored Procedures, Triggers, and Rules

DBMS2

Database Management System 2 - SQL

SQL Stored Procedures, Triggers, and Rules Automation Nom. Nom. Nom.

In Theory • We create primary keys and foreign keys to limit our database to accept only valid data to avoid orphan or duplicate records. • We can then automate this process by telling the database to do a set of instructions if these rules are violated.

Creating a Stored Procedure a.k.a. Custom Function • Before creating a function, do this: • Type in the following command, just like pg_dump: • createlang --username=postgres plpgsql

• This is so that we can use the plpgsql (procedural) language for your database.

Creating a Stored Procedure a.k.a. Custom Function CREATE FUNCTION roomrenter(roomid numeric) RETURNS varchar AS $$ DECLARE renter varchar; BEGIN SELECT est_name INTO renter FROM renters JOIN stalls ON renters.c_renter = stalls.c_renter WHERE rm_id = roomid; RETURN renter; END; $$ LANGUAGE plpgsql;

Creating a Stored Procedure a.k.a. Custom Function USING THE FUNCTION: test_db=# select roomrenter(101); roomrenter ---------------------------------------------------Jollibee (1 row)

(based on the property database)

Trigger Procedures • Use the created functions to control data in a relationship • E.g., if the masterfile record is deleted, delete all of the child records.

Trigger Procedures CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE funcname ( arguments )

Trigger Procedures • SPECIAL VARIABLES • NEW • Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers.

• OLD • Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers.

• TG_NAME • Data type name; variable that contains the name of the trigger actually fired.

• TG_WHEN • Data type text; a string of either BEFORE or AFTER depending on the trigger's definition.

Trigger Procedures • TG_LEVEL • Data type text; a string of either ROW or STATEMENT depending on the trigger's definition.

• TG_OP • Data type text; a string of INSERT, UPDATE, or DELETE telling for which operation the trigger was fired.

• TG_RELID • Data type oid; the object ID of the table that caused the trigger invocation.

• TG_RELNAME • Data type name; the name of the table that caused the trigger invocation.

• TG_NARGS • Data type integer; the number of arguments given to the trigger procedure in the CREATE TRIGGER statement.

• TG_ARGV[] • Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indices (less than 0 or greater than or equal to tg_nargs) result in a null value.

Quick Example CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*; RETURN NEW; END IF; RETURN NULL; END; $emp_audit$ LANGUAGE plpgsql; CREATE TRIGGER emp_audit AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Create Rule CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } Within condition and command, the special table names NEW and OLD may be used to refer to values in the referenced table. NEW is valid in ON INSERT and ON UPDATE rules to refer to the new row being inserted or updated. OLD is valid in ON UPDATE and ON DELETE rules to refer to the existing row being updated or deleted.

Quick Example CREATE RULE "_RETURN" AS ON SELECT TO t1 DO INSTEAD SELECT * FROM t2;

CREATE RULE "_RETURN" AS ON SELECT TO t2 DO INSTEAD SELECT * FROM t1; (Obviously this is a cycle, and would cause an error.