mysql 触发器 动态sql_MySQL触发器怎么变相使用动态SQL

用户：赵鹏433

2017年02月06日

MySQL触发器动态sql： CREATE TRIGGER trigger1 after INSERT ON cdr FOR EACH ROW BEGIN DECLARE id BIGINT; DECLARE vname VARCHAR(200); DECLARE destination VARCHAR(200) DEFAULT 'null'; DECLARE rows1 BIGINT DEFAULT 0; DECLARE sql1 VARCHAR(2000); SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve WHERE substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1; SET @@sql_mode='ansi'; SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")'); IF (id >0) THEN call testing(sql1); ELSE SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8... MySQL触发器动态sql：

CREATE TRIGGER trigger1 after INSERT ON cdr

FOR EACH ROW

BEGIN

DECLARE id BIGINT;

DECLARE vname VARCHAR(200);

DECLARE destination VARCHAR(200) DEFAULT 'null';

DECLARE rows1 BIGINT DEFAULT 0;

DECLARE sql1 VARCHAR(2000);

SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination

FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve

WHERE

substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1;

SET @@sql_mode='ansi';

SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');

IF (id >0) THEN

call testing(sql1);

ELSE

SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');

call testing(sql1);

END IF;