MySQL Forums
Forum List  »  Triggers

MYSQL Triggers Not Firing
Posted by: Maria Maskell
Date: May 28, 2015 06:25AM

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$$

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL Triggers Not Firing
3686
May 28, 2015 06:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.