PostgreSQL Triggers Lab II

Tom Kelliher, CS 318

Apr. 10, 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 a semantic constraint for a payroll database. The constraint is: No employee should earn more than his or her manager. You will then implement a second trigger and stored procedure. This trigger will maintain the constraint that no manager should earn more than the sum of the salaries of those she or he manages. These will be separate, but concurrent, triggers.

1. On phoenix, copy the file ~kelliher/pub/cs318/triggerLab2 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 first be writing the code for CheckEmployeeFunc(). If the salary in question is greater than the manager's salary, cap the salary to the manager's salary and raise a notice. The cap is ignored if the employee has no manager, but a notice should be raised.

4. 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:

   DROP
   DROP
   DROP
   psql:triggerLab2:21: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
   index 'payroll_pkey' for table 'payroll'
   CREATE
   CREATE
   CREATE
   psql:triggerLab2:62: NOTICE:  Adam has no manager.
   INSERT 52738 1
   psql:triggerLab2:64: NOTICE:  Jessie has no manager.
   INSERT 52739 1
   psql:triggerLab2:66: NOTICE:  Tom has no manager.
   INSERT 52740 1
   UPDATE 1
   UPDATE 1
   

   (Continued on next page.)

   psql:triggerLab2:71: NOTICE:  Adjusting salary for Jessie.
   UPDATE 1
    id |  name  | sid | salary 
   ----+--------+-----+--------
     1 | Tom    |     | 100000
     2 | Adam   |   1 |  75000
     3 | Jessie |   1 | 100000
   (3 rows)
   
   psql:triggerLab2:75: NOTICE:  Tom has no manager.
   UPDATE 1
    id |  name  | sid | salary 
   ----+--------+-----+--------
     2 | Adam   |   1 |  75000
     3 | Jessie |   1 | 100000
     1 | Tom    |     | 200000
   (3 rows)
   

5. Create a trigger, CheckManagerTrigger, and stored procedure, CheckManagerFunc(), to enforce the constraint that no manager earn more than the sum of his or her employees' salaries. When adjusting a salary, raise a notice. This trigger should enable on insert or update events, should be a row-level trigger, and the condition should be checked prior to the occurrence of the event.

6. 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 triggerLab2
   DROP
   DROP
   DROP
   DROP
   DROP
   psql:triggerLab2:23: NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit
   index 'payroll_pkey' for table 'payroll'
   CREATE
   CREATE
   CREATE
   CREATE
   CREATE
   psql:triggerLab2:88: NOTICE:  Adam has no manager.
   INSERT 52654 1
   psql:triggerLab2:90: NOTICE:  Jessie has no manager.
   INSERT 52655 1
   psql:triggerLab2:92: NOTICE:  Tom has no manager.
   INSERT 52656 1
   

   (Continued on next page.)

   UPDATE 1
   UPDATE 1
   psql:triggerLab2:97: NOTICE:  Adjusting salary for Jessie.
   UPDATE 1
    id |  name  | sid | salary 
   ----+--------+-----+--------
     1 | Tom    |     | 100000
     2 | Adam   |   1 |  75000
     3 | Jessie |   1 | 100000
   (3 rows)
   
   psql:triggerLab2:101: NOTICE:  Adjusting salary for Tom
   psql:triggerLab2:101: NOTICE:  Tom has no manager.
   UPDATE 1
    id |  name  | sid | salary 
   ----+--------+-----+--------
     2 | Adam   |   1 |  75000
     3 | Jessie |   1 | 100000
     1 | Tom    |     | 175000
   (3 rows)