I am new to Triggers and am facing some lock issue on TransactionDateTracking table (duplicate entry message on inserting in TransactionDateTracking) ) when I used triggers. The following are the table structures
CREATE TABLE TransactionDateTracking (
Id int(11) NOT NULL AUTO_INCREMENT,
TransactionTrackType varchar(50) NOT NULL,
TransactionTrackDate date NOT NULL,
LastRunDate datetime DEFAULT NULL,
PRIMARY KEY (Id),
UNIQUE KEY TransactionTrackType_UNIQUE (TransactionTrackType)
) ENGINE=InnoDB AUTO_INCREMENT=645 DEFAULT CHARSET=latin1;
CREATE TABLE JournalJobLog (
TransformationId int(11) NOT NULL AUTO_INCREMENT,
TransformationName varchar(255) NOT NULL,
LastUpdated timestamp NULL DEFAULT NULL,
LastFailureDate timestamp NULL DEFAULT NULL,
RevisionNumber bigint(20) DEFAULT NULL,
PRIMARY KEY (TransformationName),
KEY IDX_JournalJobLog (TransformationId)
) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=utf8;
The below are two triggers which I used - DROP TRIGGER IF EXISTS JournalJobLogInsert;
DELIMITER $$
CREATE TRIGGER JournalJobLogInsert AFTER INSERT ON TransactionDateTracking FOR EACH ROW
BEGIN
INSERT INTO JournalJobLog
SET TransformationName=NEW.TransactionTrackType,
LastUpdated=NEW.TransactionTrackDate;
END$$
DELIMITER ;
===============================================================
DROP TRIGGER IF EXISTS JournalJobLogUpdate;
DELIMITER $$
CREATE TRIGGER JournalJobLogUpdate AFTER UPDATE ON TransactionDateTracking FOR EACH ROW
BEGIN
INSERT INTO JournalJobLog (TransformationName, LastUpdated) VALUES(NEW.TransactionTrackType, NEW.TransactionTrackDate) ON DUPLICATE KEY UPDATE
LastUpdated=NEW.TransactionTrackDate;
END$$
DELIMITER ;
The problem is am unable to insert any entry in TransactionDateTracking stating "Duplicate entry" message sometime. When I drop the triggers it works fine.