Triggers

• Once again, this is MySQL specific.
• Triggers are stored programs.
• They are called when a specific event occurs.
  • Insert, Update, Delete.
  • Before or After the event.
• Other DBMS products also support InsteadOf
• SHOW TRIGGERS
• DROP TRIGGER trigger_name
• A silly example
  • Create a database to track the changes:

  • CREATE TABLE WAREHOUSE_AUDIT(
        TransID INT NOT NULL AUTO_INCREMENT,
        Action CHAR(5),
        OldName VARCHAR(40),
        NewName VARCHAR(40),
        PRIMARY KEY (TransID));
    	

  • Now add two triggers to the WAREHOUSE table.

    CREATE TRIGGER beforeWarehouseUpdate
       BEFORE UPDATE ON WAREHOUSE
         FOR EACH ROW BEGIN
            INSERT INTO WAREHOUSE_AUDIT
        	   (Action, OldName, NewName)
    	    VALUES ('before', OLD.Manager, NEW.manager);
    END;
    
    CREATE TRIGGER afterWarehouseUpdate
       AFTER UPDATE ON WAREHOUSE
         FOR EACH ROW BEGIN
            INSERT INTO WAREHOUSE_AUDIT
        	   (Action, OldName, NewName)
    	   VALUES ('after', OLD.Manager, NEW.manager);
    END;
    
    	

  • Then change the WAREHOUSE database:

    UPDATE WAREHOUSE SET Manager="Roy Rogers" WHERE Manager LIKE "%Rogers";
    SELECT * from WAREHOUSE_AUDIT;
    	

    • We can change the values of NEW.*
    • In this case, I don't want any warehouses in PA.

      CREATE TRIGGER beforeWarehouseInsert
         BEFORE INSERT ON WAREHOUSE
         FOR EACH ROW BEGIN
           IF NEW.WarehouseState = "PA" THEN
               SET NEW.WarehouseState = "XX";
           END IF;
         END;
      
      INSERT INTO WAREHOUSE 
         (WarehouseCity, Manager, SquareFeet, WarehouseState) 
         VALUES ("Edinboro", "Dan Bennett", 100, "PA")//
          

    • If a trigger raises an exception, it will abort the transaction.
      • In newer versions on MySQL there is a singal structure
      • SIGNAL SQLSTATE statevalue SET MESSAGE_TEXT = message
      • But this is not in 5.1
      • So just create a bad procedure call.
      • BOY DO I NOT LIKE THIS.
      • But this will not allow warehouses in OH

        CREATE TRIGGER afterWarehouseInsert
           AFTER INSERT ON WAREHOUSE
           FOR EACH ROW BEGIN
              IF NEW.WarehouseState = "OH" THEN
                   CALL No_Warehouses_Permitted_in_Ohio;
              END IF;
           END;