Description:
The trigger throw exception; when I do not add DML statement in the begining like " update dumy set x=temp"; kindly see the following trigger, it should work with out any false DML statment.
CREATE TRIGGER ProcessCDR2 before INSERT ON nextone2
FOR EACH ROW
BEGIN
DECLARE destination VARCHAR(200) DEFAULT \"\";
DECLARE id BIGINT DEFAULT 0;
DECLARE vname VARCHAR(200) ;
DECLARE temp VARCHAR(200) DEFAULT IF (NEW.CallDestRegid=\"\",'n/a',NEW.CallDestRegid);
update dumy set x=temp ;
SELECT v1.vendorid,v1.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID, EndPoint from vendor v natural join vendorendpoints ) v1
RIGHT JOIN
(
SELECT IF(`CountryCode`=`OperatorList`,length(`Prefix`),length(`Prefix`)+length(`OperatorList`)) as length,`DestinationID`,`VendorID`,`Prefix`,`OperatorList`,`CountryCode`
from vendorextraction order by length desc)
ve
ON (v1.VendorID=ve.VendorID)
WHERE
substr(NEW.CalledStationId , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length)
AND
v1.EndPoint=temp
LIMIT 1;
IF (id IS NOT NULL AND id >0 OR destination <> \"\") then
insert into ncdr2 (`VendorID`, `name` ,`destination`, `ip`, `duration`,`errortype`,`Called-Party`,`DATE`,`EndPoint`) values( id, vname, destination, NEW.NASIPAddress, NEW.SessionTime, NEW.DisconnectErrorType, NEW.CalledStationId, NEW.CreationDate, temp );
ELSE
insert into unknown2 (`VendorID`, `name` ,`destination`, `ip`, `duration`,`errortype`,`Called-Party`,`DATE`,`EndPoint`) values( id, vname, destination, NEW.NASIPAddress, NEW.SessionTime, NEW.DisconnectErrorType, NEW.CalledStationId, NEW.CreationDate, temp );
END IF;
END
How to repeat:
CREATE TRIGGER ProcessCDR2 before INSERT ON nextone2
FOR EACH ROW
BEGIN
DECLARE destination VARCHAR(200) DEFAULT \"\";
DECLARE id BIGINT DEFAULT 0;
DECLARE vname VARCHAR(200) ;
DECLARE temp VARCHAR(200) DEFAULT IF (NEW.CallDestRegid=\"\",'n/a',NEW.CallDestRegid);
update dumy set x=temp ;
SELECT v1.vendorid,v1.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID, EndPoint from vendor v natural join vendorendpoints ) v1
RIGHT JOIN
(
SELECT IF(`CountryCode`=`OperatorList`,length(`Prefix`),length(`Prefix`)+length(`OperatorList`)) as length,`DestinationID`,`VendorID`,`Prefix`,`OperatorList`,`CountryCode`
from vendorextraction order by length desc)
ve
ON (v1.VendorID=ve.VendorID)
WHERE
substr(NEW.CalledStationId , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length)
AND
v1.EndPoint=temp
LIMIT 1;
IF (id IS NOT NULL AND id >0 OR destination <> \"\") then
insert into ncdr2 (`VendorID`, `name` ,`destination`, `ip`, `duration`,`errortype`,`Called-Party`,`DATE`,`EndPoint`) values( id, vname, destination, NEW.NASIPAddress, NEW.SessionTime, NEW.DisconnectErrorType, NEW.CalledStationId, NEW.CreationDate, temp );
ELSE
insert into unknown2 (`VendorID`, `name` ,`destination`, `ip`, `duration`,`errortype`,`Called-Party`,`DATE`,`EndPoint`) values( id, vname, destination, NEW.NASIPAddress, NEW.SessionTime, NEW.DisconnectErrorType, NEW.CalledStationId, NEW.CreationDate, temp );
END IF;
END
