MySQL Trigger Not Working – Troubleshooting Guide

MySQLtrigger

I need some help identifying what I did wrong.

I created two tables to track history from my main table. When I update a state from 0 to 1, the first trigger copies the data into history table 1.

But when I change the state from 1 to 2, the second trigger is not triggering, thus not copying the data into history table 2.

CREATE TABLE stateOneHistoryTable (
  historyDate DATETIME DEFAULT NULL,
  eventID int(11) NOT NULL,
  eventTIME decimal(15,0) NOT NULL,
  newestEventTime decimal(20,0) DEFAULT NULL,
  deviceIP varchar(20) NOT NULL,
  syslogMessage varchar(1000) NOT NULL,
  syslogState decimal(2,0) NOT NULL,
  eventCount decimal(5,0) NOT NULL
)

CREATE TABLE stateTwoHistoryTable (
  historyDate DATETIME DEFAULT NULL,
  eventID int(11) NOT NULL,
  eventTIME decimal(15,0) NOT NULL,
  newestEventTime decimal(20,0) DEFAULT NULL,
  deviceIP varchar(20) NOT NULL,
  syslogMessage varchar(1000) NOT NULL,
  syslogState decimal(2,0) NOT NULL,
  eventCount decimal(5,0) NOT NULL
)

Below are the triggers I created.

DELIMITER //

CREATE TRIGGER stateOneHistoryTableTrigger
AFTER UPDATE
ON syslog 
FOR EACH ROW

BEGIN
    IF (old.syslogState=0 & new.syslogState=1)
        THEN
            INSERT INTO stateOneHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
            VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);
    END IF;
END; //

DELIMITER ; 




DELIMITER //

CREATE TRIGGER stateTwoHistoryTableTrigger
AFTER UPDATE
ON syslog 
FOR EACH ROW

BEGIN
    IF (old.syslogState=1 & new.syslogState=2)
        THEN
            INSERT INTO stateTwoHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
            VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);
    END IF;
END; //

DELIMITER ;

Best Answer

Are you sure stateTwoHistoryTableTrigger exists? I'm asking since at least some versions of MySQL don't support multiple triggers with the same action time and event for the same table. You can check if the trigger exists like:

select trigger_name 
from information_schema.triggers 
where EVENT_OBJECT_TABLE = 'syslog';

If that is the problem you can add both actions in one trigger like:

DELIMITER //

CREATE TRIGGER stateOneHistoryTableTrigger
AFTER UPDATE ON syslog
FOR EACH ROW
BEGIN
    IF (old.syslogState=0 & new.syslogState=1)
    THEN
        INSERT INTO stateOneHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
        VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);

ELSEIF (old.syslogState=1 & new.syslogState=2)
    THEN
        INSERT INTO stateTwoHistoryTable (historyDate, eventID, eventTIME, newestEventTime, deviceIP, syslogMessage, syslogState, eventCount)
        VALUES (utc_timestamp(), new.eventID, new.eventTIME, new.newestEventTime, new.deviceIP, new.syslogMessage, new.syslogState, new.eventCount);

    END IF;
END; //

DELIMITER ;

May I ask why you want two history tables? If you keep all history in one table you can derive the two history tables from there:

CREATE TABLE stateHistoryTable (
    historyDate DATETIME DEFAULT NULL,
    eventID int(11) NOT NULL,
    eventTIME decimal(15,0) NOT NULL,
    newestEventTime decimal(20,0) DEFAULT NULL,
    deviceIP varchar(20) NOT NULL,
    syslogMessage varchar(1000) NOT NULL,
    syslogState decimal(2,0) NOT NULL,
    eventCount decimal(5,0) NOT NULL
) engine=innodb;

CREATE TRIGGER stateHistoryTableTrigger
AFTER UPDATE ON syslog
FOR EACH ROW
BEGIN
    IF (old.syslogState + 1 = new.syslogState)
    THEN INSERT ...
END IF; 

CREATE VIEW stateOneHistoryTableTrigger AS
    SELECT ...
    FROM stateHistoryTable
    WHERE syslogState = 1;

CREATE VIEW stateTwoHistoryTableTrigger AS
    SELECT ...
    FROM stateHistoryTable
    WHERE syslogState = 2;
Related Question