MySQL触发器怎么变相使用动态SQL
1个回答
2017-01-11 · 做真实的自己 用良心做教育
千锋教育
千锋教育专注HTML5大前端、JavaEE、Python、人工智能、UI＆UE、云计算、全栈软件测试、大数据、物联网+嵌入式、Unity游戏开发、网络安全、互联网营销、Go语言等培训教育。
向TA提问
关注
![]()
展开全部
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;
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;
已赞过
已踩过<
评论
收起
你对这个回答的评价是？
推荐律师服务：
若未解决您的问题，请您详细描述您的问题，通过百度律临进行免费专业咨询
