Lab #11 Worksheet – Triggers, & Cursors

Triggers

A trigger is procedural SQL code associated with a given table, and is automatically executed before or after some update/insertion/deletion on that table (triggering event). A trigger can be used to enforce constraints, to automate critical actions (like updating some derived attribute). A trigger can update values, insert records, and call procedures. A table may have multiple triggers that are fired on different events and can do different things. Triggers add processing capabilities to a database application (event-based actions).

To create a new trigger in PostgreSQL, you follow these steps:

• First, create a trigger function using CREATE FUNCTION statement.
• Second, bind the trigger function to a table by using CREATE TRIGGER statement.

CREATE FUNCTION trigger_function()
RETURNS TRIGGER
LANGUAGE PLPGSQL 
AS $$ BEGIN					  
-- trigger logic
END;
$$;

A trigger function receives data about its calling environment through a special structure called TriggerData which contains a set of local variables. For example, OLD and NEW represent the states of the row in the table before or after the triggering event.

CREATE TRIGGER trigger_name {BEFORE | AFTER} { event }
 ON table_name
 [FOR [EACH] { ROW | STATEMENT }]		
EXECUTE PROCEDURE trigger_function

• First add a new smallint attribute called reorder in the table Product. The new attribute will have value 1 when the quantity on hand for a given product falls below the minimum allowed quantity on hand.

ALTER TABLE Product ADD COLUMN reorder smallint;

• Now create the following trigger:

CREATE FUNCTION update_prod() RETURNS TRIGGER
AS $$
BEGIN
UPDATE Product
SET reorder = 1
WHERE productCode = NEW.productCode;
RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trProdUpd AFTER UPDATE ON
Product FOR EACH ROW
WHEN (OLD.quantityOnHand IS DISTINCT FROM NEW.quantityOnHand
AND NEW.quantityOnHand < NEW.minQuantityOnHand) EXECUTE FUNCTION update_prod();

• Now pick a product that has reorder different than 1, and decrease its quantity on hand so that it becomes less than its minQoH. For example:

UPDATE Product
    SET quantityOnHand = quantityOnHand - 9
    WHERE productCode = '1546-QQ2';

Check that product’s row values.

What happened? Please note that this trigger may need improvement.

• In this example, a trigger will be used to maintain a derived attribute (amount) in the Invoice table:
  • Add attribute amount of type decimal(8, 2) (not nullable) to table Invoice, with a defaultvalue 0. Give the SQL statement to do that.
  • Update the amount attribute to be the sum of the corresponding total line prices. You can use a correlated subquery. Give the corresponding SQL statement.
  • Create a trigger that will automatically update (increase) the invoice amount every time a line is inserted.
  • Insert some invoice line for some existing invoice and check the effect on the corresponding invoice.
• Create a trigger that would update (increase or decrease) the customer balance every time an amount is invoiced for that customer (example trigger on the handout online).