Triggers (Basi di Dati)

triggers.sql

-- Marco Montanari 05/04/2022
-- https://dbdmg.polito.it/wordpress/wp-content/uploads/2020/05/trigger-BD.pdf

-- Inventory (_PartNr_, QtyOnHand, ThresholdQty, ReorderQty)
-- PendingOrders(_PartNr_, OrderDate, OrderedQty)

-- when the stocked quantity of a product goes
-- below a given threshold
-- a new order for the product should be issued

-- Event:
--        - Update of the quantity on hand for product x
--        - Insert of a new product x
-- Mode:
--        - After the modification event
-- Granularity:
--        - Separate execution for each row of the inventory table  
-- Condition:
--        - The quantity on hand is below a given threshold
--        - There are no pending orders for product x
-- Action:
--        - Issue an order with given reorder quantity for product x

CREATE TRIGGER CheckThresholdQty
    AFTER
        INSERT
        OR UPDATE OF QtyOnHand
        ON Inventory
    FOR EACH ROW
    WHEN NEW.QtyOnHand < NEW.ThresholdQty
    DECLARE
        N number;
    BEGIN
        -- Check if there are pending orders for the product
        SELECT COUNT(*) INTO N
        FROM PendingOrders
        WHERE PartNr = :NEW.PartNr;

        -- If there are no pending orders...
        IF (N = 0) THEN
            -- Insert into pending orders the product
            INSERT INTO PendingOrders(PartNr, OrderedQty, OrderDate)
            VALUES (:NEW.PartNr, :NEW.ReorderQty, SYSDATE);
        END IF;
    END;


-- Employee (_EmpNr_, Ename, ..., Salary)

-- When a given average salary value is exceeded,
-- a salary reduction is automatically enforced

-- Event:
--        - Update of the salary attribute in employee
--        - Insert into employee
-- Mode:
--        - After the modification events
-- Granularity:
--        - Separate execution for each update instruction  
-- Condition:
--        - No condition
-- Action:
--        - Issue an order with given reorder quantity for product x
CREATE TRIGGER SalaryMonitor
    AFTER
        UPDATE OF Salary
        ON Employee
    -- FOR EACH STATEMENT -- Optional
    BEGIN
        UPDATE EMPLOYEE
        SET SALARY = SALARY * K
        WHERE 2500 < (
            SELECT AVG(Salary)
            FROM Employee
        );

        -- If K is >= 1, then this cycles forever
        -- If K is < 1, it will execute until the average salary is < 2500
    END;


-- Supplier S (_SNr_, SName, ...)
-- Part P (_PNr_, PName, ...)
-- Supply SP (_SNr_, _PNr_, Qty)

-- A part may be supplied by at most 10 different
-- suppliers

-- Event:
--        - Insert on SP
--        - Update of PNr on SP
-- Mode:
--        - After the modification
-- Granularity:
--        - Separate execution for each statement,
--          because we need to count values of SP  
-- Condition:
--        - There are more than 10 of the same PNr in SP
-- Action:
--        - Reject the violating transaction
CREATE TRIGGER TooManySuppliers
    AFTER
        INSERT
        OR UPDATE OF PNr
        ON SP
    DECLARE
        N number;
    BEGIN
        SELECT COUNT(*) INTO N
        FROM SP
        WHERE PNr In (
            SELECT PNr
            FROM SP
            GROUP BY PNr
            HAVING COUNT(*) > 10
        );

        -- Se ci sono più di 10 suppliers per almeno 1 product
        IF (N > 0) THEN
            -- Lancia un'eccezione (e quindi un rollback)
            raise_application_error(xxx, 'contraint violated');
        END IF;
    END;


-- Supplier S (_SNr_, SName, ...)
-- Part P (_PNr_, PName, ...)
-- Supply SP (_SNr_, _PNr_, Qty)

-- The quantity of a product supply cannot be larger
-- than 1000. If it is larger, trim it to 1000.

-- Event:
--        - Update of the quantity of the Supply
--        - Insert of a new product Supply
-- Mode:
--        - Before the event
-- Granularity:
--        - Separate execution for each row of the Supply table  
-- Condition:
--        - The quantity is larger than 1000
-- Action:
--        - The quantity is set to 1000
CREATE TRIGGER ExcessiveQty
    BEFORE
        INSERT
        OR UPDATE OF Qty
        ON SP
    FOR EACH ROW
    WHEN NEW.Qty > 1000
    BEGIN
        :NEW.Qty := 1000;
    END;

Rakefile

load 'tasks/emoji.rake'