THE PROJECT

Personal Database Application, Part 6

Triggers are discussed in the document Functions and Triggers and in the document Triggers. See also CREATE TRIGGER For more information on creating trigger functions see CREATE FUNCTION and PL/pgSQL - SQL Procedural Language. View definitions and constraint declarations largely follow the SQL2 standard although there are some restrictions; see the document Views and Constraints for more details.

In order to use plpgsql for your trigger functions, prior to starting psql use the command:

createlang -U postgres -L /usr/lib/pgsql plpgsql dbname

(a) Create two useful views on top of your PDA database schema. Submit a file views.sql containing the CREATE VIEW statements. Also submit a script file 6a.log showing the response of the system to the view definitions, and for each view, showing a query involving the view and the system response. (As usual you should truncate the response if more than a few tuples are produced.)

(b) You are to recreate your PDA schema, adding specifications for additional keys, referential integrity, and other constraints.

1. Modify your PDA CREATE TABLE statements as follows.

   • For each relation in your schema, if the relation has any keys in addition to the PRIMARY KEY that you already declared, specify the additional keys as UNIQUE. If there are no additional keys, add a comment to that effect.

   • For each referential integrity constraint that should hold in your schema, specify the constraint using a REFERENCING clause within the appropriate CREATE TABLE statement. You may use the default option for handling referential integrity violations (violations will generate an error). We expect that everyone's PDA should include at least one referential integrity constraint. If your PDA has no natural referential integrity constraints, then it probably is either far simpler than we asked for, or a poor design - please contact one of the course staff.

   • Add at least two attribute-based and two tuple-based CHECK constraints to relations of your database schema. Remember that these constraints may be more limited in PostgreSQL than in the SQL2 standard.

   Submit a file cons.sql containing all your CREATE TABLE statements, along with a script file 6b1.log showing their successful execution in PostgreSQL.

2. Reload your small PDA database. Did you get any key, referential integrity, or CHECK constraint violations?

3. Reload your large PDA database. Did you get any key, referential integrity, or CHECK constraint violations?

4. You don't necessarily need to modify your program for generating the large data set if it creates violations. However, for this part of the problem you should start with a database (small or large) that does not create violations. Write data modification commands to illustrate the following seven scenarios:

   • An INSERT command creating a key violation
   • An UPDATE command creating a key violation
   • An INSERT command creating a referential integrity violation
   • A DELETE command creating a referential integrity violation
   • An UPDATE command creating a referential integrity violation
   • An INSERT command creating a CHECK constraint violation
   • An UPDATE command creating a CHECK constraint violation

   Submit a file viols.sql containing all seven commands, along with a script file 6b2.log showing their unsuccessful execution in PostgreSQL. (c) Create at least two "interesting" triggers for your PDA. Submit a file trigs.sql containing the CREATE TRIGGER statements. Also submit a script file 6c.log showing the response of the system to the trigger definitions, and the execution of database modifications that illustrate the firing of each trigger and cases where neither trigger fires. Show in your script the results of queries demonstrating that the triggers had an effect when they fired and no effect when they didn't.

   (d) Extra credit problem: If you do a good job on this problem you are eligible for up to an extra 30% of the total points for this programming assignment.

   You are to do some sleuth work: Your task is to determine what criteria exactly PostgreSQL uses in deciding whether a view is updatable, i.e., whether it is possible to perform INSERT, DELETE, and/or UPDATE statements on the view. While your sleuth work could involve sifting through HELP pages or PostgreSQL books, we prefer that you do it experimentally. Write a series of views along with modification commands on the views to determine when PostgreSQL allows views to be updated and when it does not. As discussed in class and in the textbook, some SQL views are obviously updatable, some are obviously not updatable (due to ambiguities), and some are theoretically updatable but it is difficult for a system to determine the correct update translations. In your solution to this problem you should attempt to provide a concise characterization of those views that PostgreSQL allows to be updated, and you should support your claim by demonstrating:

   • views meeting the criteria that can be updated, and
   • views not meeting the criteria that cannot be updated.

   If separate criteria apply for INSERT, DELETE, and UPDATE commands then these should be included in your solution. You may use your PDA schema and data for this problem if you like, or you may use a separate, simpler database.

   If you attempt this problem, please submit a text file extra.txt specifying the view update criteria you believe PostgreSQL, and specifying clearly the contents of submitted .sql and/or .log files that support your findings.

Submission

README
views.sql
6a.log
cons.sql
6b1.log
viols.sql
6b2.log
trigs.sql
6c.log

Unless otherwise specified, the scripts you turn in for this assignment may show operations running over your small or your large database (or in the case of Part (d), another database altogether).

• If you use your small database, be sure to test your work on your large database as well to make sure it is correct and reasonably efficient.
• If you use your large database, please do not submit listings showing hundreds of lines of query results.

Example Trigger using PL/PGSQL

Whenever we insert a new tuple into Sells, remove white space and convert to upper case the beer name and then make sure the beer mentioned is also mentioned in Beers, and insert it (with a null manufacturer) if not.

Sells(bar, beer, price)

CREATE FUNCTION checkForBeer()
RETURNS opaque
AS 'BEGIN
 IF length(trim(new.beer)) > 0
 THEN
 new.beer = upper(trim(new.beer)); 
 END IF;
	IF new.beer NOT IN
	(SELECT name FROM Beers)
 THEN
 INSERT INTO Beers(name) 
 VALUES(new.beer);
 END IF;
 RETURN new;
END;'
LANGUAGE 'plpgsql';

CREATE TRIGGER BeerTrig
BEFORE INSERT OR UPDATE ON Sells
FOR EACH ROW
EXECUTE PROCEDURE checkForBeer();

Additional reference materials from updated lecture notes

PostgreSQL Triggers and Trigger Functions

• Action Block in Oracle style trigger is replaced with procedure call.
• Procedure is a persistent Stored module also called Server-side trigger function.
• PostgreSQL supports functions written in PL/PGSQL, PL/TCL, PL/Perl and C.
• The trigger function must be created before the trigger is created as a function taking no arguments and returning opaque.
• Server-side trigger functions have predefined variables to access the row that caused the trigger. In PL/PGSQL these are new and old.
• 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.
• BEFORE Triggers that return NULL  cause the operation that caused the trigger to be ignored. Otherwise, the returned record/row replaces the inserted/updated row in the operation
• With PL/PGSQL it is possible to replace single values directly in new and return that or to build a complete new record/row to return
• Triggers fired AFTER may always return a NULL value with no effect.

PostgreSQL CREATE TRIGGER Syntax:

CREATE TRIGGER trigger 
[ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ] 
ON relation FOR EACH [ ROW | STATEMENT ] 
EXECUTE PROCEDURE procedure (args); 

PL/PGSQL Functions in PostgreSQL

• PL/PGSQL is PostgreSQL own version of PL/SQL. It is used for server side functions that are more complex than those in SQL.
• PL/PGSQL can be used to create functions and trigger procedures,
• adds control structures such as IF THEN ELSE and WHILE and FOR LOOPs to the SQL language,
• can perform complex computations,
• can use all the data types, columns, operators and functions of SQL.
• inherits all user defined types, functions and operators,
• can be defined to be trusted by the server.
• Block structured using BEGIN – END. Blocks can be nested.
• Optional DECLARE section for each block to define variables
• %TYPE and %ROWTYPE can be used to declare variables to match types in data base.
• Functions must have a return value. “RETURN expresion”is used. The expressions result will be automatically cast into the function's return type.
• Can use SELECT to execute a function. Can use PERFORM within functions if you are not interested in the results.
• Can use EXECUTE to execute dynamic queries generated within a function.