The following is a working version of a trigger that enforces referential integrity. This is a 'Before Insert' trigger. A similar 'Before Update' trigger is also created. The only unusual portion of the code is the use of the TriggerError table which is simply a table used to store the error message. This table has a unique key on the message column. Since currently does not offer a way to raise an error in a trigger, we utilize the duplicate key error when inserting the message to actually display the message. This is obviously not the best way but it is the best way currently available.

 

Note the keywords NEW.{fieldName}. This represents the incoming value to be inserted. This value can be modified in the trigger by setting the NEW.{fieldName} to the new value. This new value will be the value inserted into the database. In an 'update' or 'delete' trigger, you also have the OLD.{fieldName} which represents the value of the field before being updated or deleted.

 

 

 

use UrDb;

DROP TRIGGER TableNameBeforeInsert;

DELIMITER |

CREATE TRIGGER TableNameBeforeInsert

BEFORE INSERT ON TableName

  FOR EACH ROW

    BEGIN

      -- ----------------------------------------------------------------

      -- Set dateInserted / dateUpdated audit values

      -- ----------------------------------------------------------------

      SET NEW.dateInserted = now();

      SET NEW.dateUpdated = now();

      SET @EntityID = 0;

      SET @QualifierID = 0;

      -- ----------------------------------------------------------------

      -- Validate if Entity id is valid

      -- ----------------------------------------------------------------

      SELECT id INTO @ EntityID 

      FROM Entity

      WHERE id = NEW.entityID;

      IF (@ EntityID = 0) THEN

          -- The following is a mysql hack because mysql does not offer a way to exit out of a trigger.

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Entity id') ;

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Entity id') ;

      END IF;

 

      -- ----------------------------------------------------------------

      -- Validate if Qualifier Type is valid

      -- ----------------------------------------------------------------

      IF (NEW.qualifierType != 'Product' AND NEW.qualifierType != 'Brand' AND NEW.qualifierType != 'Category') THEN

          -- The following is a mysql hack because mysql does not offer a way to exit out of a trigger.

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Type') ;

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Type') ;

      END IF;

      IF (NEW.qualifierType = 'Product') THEN

        -- ----------------------------------------------------------------

        -- Validate if Product id is valid

        -- ----------------------------------------------------------------

        SELECT id INTO @QualifierId

        FROM Product

        WHERE id = NEW.qualifierId;

        IF (@QualifierId = 0) THEN

          -- The following is a mysql hack because mysql does not offer a way to exit out of a trigger.

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Product id') ;

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Product id') ;

        END IF;

      END IF;

      IF (NEW.qualifierType = 'Brand') THEN

        -- ----------------------------------------------------------------

        -- Validate if Brand id is valid

        -- ----------------------------------------------------------------

        SELECT id INTO @QualifierId

        FROM Brands

        WHERE id = NEW.qualifierId;

        IF (@QualifierId = 0) THEN

          -- The following is a mysql hack because mysql does not offer a way to exit out of a trigger.

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Brand id') ;

          insert into TriggerError (TriggerErrorMessage) values ('Invalid Brand id') ;

        END IF;