MYSQL Triggers Not Firing
Hello -
I've created an update trigger which updates another table. The trigger fires successfully if I manually submit update statements on the original table via MySQL workbench. The trigger does not fire if I use the "Edit Table Data" function of MySQL workbench. It also does not fire within our application using Alplha Anywhere. Table definition and trigger below. Troubled... Any thoughts?
Thanks,
Maria
delimiter $$
CREATE TABLE `zapdailyscheduledetail2` (
`apdsd_StartTime` varchar(10) DEFAULT NULL,
`apdsd_EndTime` varchar(10) DEFAULT NULL,
`Comp_Name` varchar(60) DEFAULT NULL,
`apdsd_DailyScheduleDetailId` int(11) DEFAULT NULL,
`Comp_CompanyId` int(11) DEFAULT NULL,
`apdsd_Date` datetime DEFAULT NULL,
`apdsd_PersonId` int(11) DEFAULT NULL,
`apdsd_ActualStart` varchar(10) DEFAULT NULL,
`apdsd_ActualEnd` varchar(10) DEFAULT NULL,
`comp_lat` varchar(50) DEFAULT NULL,
`comp_long` varchar(50) DEFAULT NULL,
`apdsd_SecTerr` int(11) DEFAULT NULL,
`Addr_AddressId` int(11) DEFAULT NULL,
`AddCitySt` varchar(124) DEFAULT NULL,
`apcc_TollFreeNumber` varchar(15) DEFAULT NULL,
`apdi_DialFromNumber` varchar(10) DEFAULT NULL,
`zID` int(11) NOT NULL AUTO_INCREMENT,
`ChangeDt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`zID`),
UNIQUE KEY `GetGuardsInfo` (`apdsd_PersonId`,`apdsd_Date`,`apdsd_StartTime`),
KEY `DetailID` (`apdsd_DailyScheduleDetailId`)
) ENGINE=InnoDB AUTO_INCREMENT=23256 DEFAULT CHARSET=utf8$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `world`.`tU_beforeupdate_zapdailyscheduledetail2`
BEFORE UPDATE ON `world`.`zapdailyscheduledetail2`
FOR EACH ROW
insert into debug (debugtxt) values ('before update');
$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `world`.`tU_zapdailyscheduledetail2`
AFTER UPDATE ON `world`.`zapdailyscheduledetail2`
FOR EACH ROW
BEGIN
declare PIN int(11);
set PIN = (select pers_Pin from visipluspers
where Visi_PersonID = new.apdsd_PersonId);
IF STRCMP(new.apdsd_ActualStart,old.apdsd_ActualStart) != 0
OR STRCMP(new.apdsd_ActualEnd,old.apdsd_ActualEnd) != 0
THEN
# ClockIN or Clock Out Occurred
INSERT INTO APTimekeeping2
(aptk_CreatedBy
,aptk_CreatedDate
,aptk_UpdatedBy
,aptk_UpdatedDate
,aptk_TimeStamp
,aptk_DNIS
,aptk_PINNumber
,aptk_ClockInOutTime
,aptk_DailyScheduleDetailID
,aptk_CallGUID
)
VALUES
(113
,new.ChangeDt
,113
,new.ChangeDt
,current_timestamp()
,new.apcc_TollFreeNumber
,PIN
,now()
,new.apdsd_DailyScheduleDetailID
,'CloudTrigger'
);
END IF;
END
$$
delimiter $$
CREATE TABLE `aptimekeeping2` (
`idAPTimekeeping` int(11) NOT NULL AUTO_INCREMENT,
`aptk_CreatedBy` int(11) DEFAULT NULL,
`aptk_CreatedDate` datetime DEFAULT NULL,
`aptk_UpdatedBy` int(11) DEFAULT NULL,
`aptk_UpdatedDate` datetime DEFAULT NULL,
`aptk_TimeStamp` timestamp NULL DEFAULT NULL,
`aptk_DNIS` varchar(10) DEFAULT NULL,
`aptk_ANI` varchar(10) DEFAULT NULL,
`aptk_PINNumber` varchar(10) DEFAULT NULL,
`aptk_ClockInOutTime` varchar(26) DEFAULT NULL,
`aptk_DailyScheduleDetailId` int(11) DEFAULT NULL,
`aptk_CallGUID` varchar(100) DEFAULT NULL,
`aptk_Synced` int(11) DEFAULT '0',
`ChangeDt` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`idAPTimekeeping`),
KEY `DetailID` (`aptk_DailyScheduleDetailId`),
KEY `NotSynced` (`aptk_Synced`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8$$