Lab 4 - Triggers

Objectives: 

• Create PL/pgSQL functions to implement semantic constraints
   

___________________________________________________________________________________________________________________________________

Structure of a PL/pgSQL Procedure

PL/pgSQL is block structured:

[ Label ]
[ DECLARE
     declarations ]
BEGIN
   statements;
END;

CREATE FUNCTION somefunc() RETURNS INTEGER AS '
DECLARE
   quantity INTEGER := 30;
BEGIN
   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 30
   quantity := 50;
   --
   -- Create a sub-block
   --
   DECLARE
      quantity INTEGER := 80;
   BEGIN
      RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 80
   END;

   RAISE NOTICE ''Quantity here is %'',quantity;  -- Quantity here is 50
END;
' LANGUAGE 'plpgsql';

Variables

PL/pgSQL has all SQL types. It is possible to create tuple variables using %ROWTYPE attribute.

Example showing this and also control structures:

DROP FUNCTION Test(INTEGER);

CREATE FUNCTION Test(INTEGER) RETURNS INTEGER AS '
DECLARE

   StudRec  Student%ROWTYPE;
   TransRec Transcript%ROWTYPE;
   Count    INTEGER := 0;
   Id       ALIAS FOR $1;

BEGIN

   -- SELECT result must be a single tuple.

   SELECT INTO StudRec *
   FROM  Student S
   WHERE S.Id = Id;

   -- Aggregate functions appear not to work.  Manually iterate over
   -- entire result set to compute count.

   FOR TransRec IN SELECT * FROM Transcript T
   WHERE StudRec.Id = T.StuId
   LOOP
      Count := Count + 1;
   END LOOP;

   IF Count < 10 THEN
      RAISE NOTICE ''% has taken too few courses'', StudRec.Name;
   END IF;

   RETURN count;
END;
' LANGUAGE 'plpgsql';

SELECT Test(666666666);

Features Specific to Trigger Procedures

Trigger procedures must be a function with no parameters and a return type of OPAQUE.

Special variables automatically created in the top-level block:

1. NEW --- new tuple value on UPDATE/INSERT row level triggers.
2. OLD --- old tuple value on UPDATE/DELETE row level triggers.

The procedure must either return NULL (or execute RAISE EXCEPTION) or a tuple matching the structure of the relation the trigger was called on.

1. BEFORE triggers return NULL to signal that the operation for this tuple should be skipped, RAISE EXCEPTION to abort the transaction, return a modified result, or do nothing (return NEW unchanged) to allow the intended result.
2. AFTER triggers can return NULL with no effect.

Example:

CREATE FUNCTION RaiseCheck () RETURNS trigger AS '
   BEGIN
      IF NEW.Salary > 1.05 * OLD.Salary THEN
         -- Excessive salary raise - limit it.
         RAISE NOTICE ''% given an excessive raise'', OLD.EmpName;
         NEW.Salary := 1.05 * OLD.Salary;
      END IF;
      -- Salary increase OK, proceed with transaction.
      RETURN NEW;
   END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER LimitRaises BEFORE UPDATE ON Employee 
   FOR EACH ROW EXECUTE PROCEDURE RaiseCheck();

___________________________________________________________________________________________________________________________________

Assignment:

1. 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.   Start by copying the files ~jillz/cs317/lab4_1 and ~jillz/cs317/lab4_2 to one of your directories.
    
2. Open this file lab4_1 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. Write the code for VerifySalary(). Carefully read the comments above this function so that you're clear on what needs to be done.
    
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:

   jillz=> \i lab4_1
   DROP TRIGGER
   DROP FUNCTION
   DROP TABLE
   CREATE TABLE
   CREATE FUNCTION
   CREATE TRIGGER
   psql:lab4_1:64: ERROR: Tom has a negative salary
   name | salary 
   ------+--------
   (0 rows)
   
   INSERT 0 1
   INSERT 0 1
   psql:lab4_1:74: NOTICE: Tom reached the salary cap
   UPDATE 1
   name | salary 
   ------+--------
   Jill | 895
   Tom | 105
   
   
   

   Some ERROR messages may be present only on the first run. Subsequently, they will be replaced with DROP messages.
    
5. In lab4_2, you will write 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.
   
    
6. 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.