trigger.sql

CREATE DEFINER=`root`@`localhost` trigger processtrigger after INSERT ON digitization_process
for each row BEGIN
UPDATE item
SET 
    item.PhysicalState = (SELECT
            NewPhysState
        FROM
            process_type p
        WHERE
            NEW.ProcessType = p.ProcessTypeID)
WHERE 
    itemid = NEW.ItemID
        AND (SELECT 
             NewPhysState 
        FROM 
             process_type p 
        WHERE 
             NEW.ProcessType = p.ProcessTypeID) IS NOT NULL;
UPDATE item 
SET 
    item.DigitalState = (SELECT 
            NewDigState
        FROM
            process_type p
        WHERE
            NEW.ProcessType = p.ProcessTypeID)
WHERE
    itemid = NEW.ItemID
        AND (SELECT 
            NewDigState
        FROM
            process_type p
        WHERE
            NEW.ProcessType = p.ProcessTypeID) IS NOT NULL;
END