A3.1 QACS SALE_ITEM Trigger

The trigger below will auto-increment the SaleItemID field when inserting a new record. A trigger has access to the incoming fields as properties of an object named NEW. Name the trigger SET_SaleItemID

When an insertion into SALE_ITEM is about to take place, the trigger will first search the SALE_ITEM table for the maximum SaleItemID field for all the records with SaleID = NEW.Sale.ID. The SaleItemID value for the new record to be inserted, shall then be this MAX(SaleItemID) + 1. If the MAX value returns NULL then that means there are no SALE_ITEM records yet for that SaleID therefore set SaleItemID = 1.

BEGIN
    SET @maxSaleItemID = (SELECT MAX(SaleItemID) FROM SALE_ITEM WHERE SaleID=NEW.SaleID);
    IF(@maxSaleItemID IS NOT NULL) THEN
        SET NEW.SaleItemID = @maxSaleItemID + 1;
    END IF;
END

Notice that the trigger code does not consider the case when there are no records in SALE_ITEM for a particular NEW.SaleID. Modify the code to set the value for NEW.SaleItemID to 1 when there are no records in SALE_ITEM for NEW.SaleID.

Implement the trigger in the SALE_ITEM table to execute before insertions. (video)