Tom Kelliher, CS 318
Apr. 8, 2002
The purpose of this lab is for you to gain some understanding of how triggers and stored procedures are used in PostgreSQL to implement integrity constraints. You will create a PL/pgSQL function which will implement two semantic constraints for a payroll database. The constraints are: No salary should be negative and the salary cap (maximum sum of the salaries) is $1,000.
~kelliher/pub/cs318/triggerLabto one of your directories.
VerifySalary(). Carefully read the comments above this function so that you're clear on what needs to be done.
psqlto open your personal database and execute the SQL code in your file. The output from the run should be very similar to:
kelliher=> \i triggerLab psql:triggerLab:5: ERROR: pg_ownercheck: class "employee" not found psql:triggerLab:6: ERROR: pg_func_ownercheck: function 'verify()' does not exist psql:triggerLab:7: ERROR: table "employee" does not exist CREATE CREATE CREATE INSERT 0 0 name | salary ------+-------- (0 rows) INSERT 45317 1 INSERT 45318 1(Continued on next page.)
psql:triggerLab:76: NOTICE: Tom reached the salary cap. UPDATE 1 name | salary ------+-------- Jill | 895 Tom | 105 (2 rows) kelliher=>The
ERRORmessages will be present only on the first run. Subsequently, they will be replaced with