MySQL Forums
Forum List  »  Triggers

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
strange thing about use trigger
1767
June 10, 2011 02:18AM


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.