PostgreSQL Triggers Lab

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.

  1. On phoenix, copy the file ~kelliher/pub/cs318/triggerLab to one of your directories.

  2. Open this file in an editor. Notice that it consists of three parts: a clean-up section; a section which creates the payroll table, PL/pgSQL function, and trigger; and a section which tests the trigger function.

  3. You will be writing the code for VerifySalary(). Carefully read the comments above this function so that you're clear on what needs to be done.

  4. Write the code for VerifySalary().

  5. When you're ready to run the code, run psql to 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
    psql:triggerLab:7: ERROR:  table "employee" does not exist
    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)
    The ERROR messages will be present only on the first run. Subsequently, they will be replaced with DROP messages.

Thomas P. Kelliher
Thu Apr 4 17:37:19 EST 2002
Tom Kelliher