strange thing about use trigger
Posted by:
h ch
Date: June 10, 2011 02:18AM
Today i find a really strange thing happened in my DB,about trigger
here is the trigger's DDL
CREATE DEFINER='trgacc'@'localhost' TRIGGER CONF_CDR after insert ON `BillLog`
FOR EACH ROW BEGIN
select Id into @ConfId from Interface_Data.Conf_List where BillNum = NEW.BillNum and CallerNum = New.CallerNum and CalledNum = New.CalledNum and CallBeginTime = '' order by Id limit 1;
if @ConfId IS NOT NULL then
update Interface_Data.Conf_List set CallBeginTime = NEW.`CallBeginTime`, CallEndTime = NEW.`CallEndTime`, CallTimer = NEW.`CallTimer` ,BillSource=NEW.`BillSource` where Id = @ConfId;
end if;
END;
after insert a row in BillLog table,the trigger will check if Conf_List table has the same value of BillNum,CallerNum and CalledNum,if so ,update the Conf_List table
but i find one record has not been updated correctly
here is the DML :
insert into BillLog ( serialNum, Billnum, CallerNum, calledNum, CallBeginTime, CallEndTime, CallTimer, BillSource, InsertTime ) values ( 'in02_L_503_001804_20110609.r8', '057187120448', '057188228130', '10000', '2011-06-09 14:18:57.000', '2011-06-09 14:19:01.000', '40', '0', '2011-6-9 14:40:04' )
and the trigger change the record on Conf_List to
*************************** 3. row ***************************
Id: 21
Conf_URL: sip:118314471350007632@pgw.ecplive.com
Conf_Subject: 123456
BillNum: 057187120448
CallerNum: 057188228130
CalledNum: 10000
CallBeginTime: 2011-06-09 14:24:50.000
CallEndTime: 2011-06-09 14:29:33.000
CallTimer: 2830
BillSource: 750
InsertTime: 2011-06-09 13:51:16
but i search the binary log ,find just one record(no other record) match the value of CallBeginTime
insert into BillLog ( serialNum, Billnum, CallerNum, calledNum, CallBeginTime, CallEndTime, CallTimer, BillSource, InsertTime ) values ( 'ws_in03_L_503_001612_20110609.r5', '057188228402', '057188228440', '053162311866', '2011-06-09 14:24:50.000', '2011-06-09 14:29:33.000', '2830', '750', '2011-6-9 14:46:49' )
that's says the trigger work wrong,but i can not locate the problem,and if u ask for the whole binary log i can send it to you ,but it's a big file (1G)