我想创建一个触发器,每当数据插入team表时,自动在teamsinfo表中删除所有数据,并重置插入红色部分代码
teamsinfo表的列数和红色部分查询列数相同且一一对应
单独执行红色部分的查询语句结果是正确的
可是这个触发器不工作,应该是没创建成功,为什么呢?create trigger teaminfotrigger
after insert
on team
for each row
begin
delete from teamsinfo where team_no like '%'
end
DELIMITER $$
begin
insert into teamsinfo
select team_no,MAX(CASE WHEN k.rownum= 2 THEN id
ELSE NULL
END) AS id1,
MAX(CASE WHEN k.rownum = 0 THEN id
ELSE NULL
END) AS id2,
MAX(CASE WHEN k.rownum = 1 THEN id
ELSE NULL
END) AS id3,
university_no as school,
`team_name` AS `team_name`,
`teacher` AS `teacher`,
`teacher_phone` AS `teacher_phone`,
`works_name` AS `works_name`,
`types` AS `types`,
`description` AS `description`
from(
SELECT
team_no,
id,
rownum() as rownum,
university_no,
`team_name` ,
`teacher` ,
`teacher_phone` ,
`works_name`,
`types` ,
`description`
FROM view1 sub) k
group by team_no
DELIMITER $$
end;
DELIMITER $$
teamsinfo表的列数和红色部分查询列数相同且一一对应
单独执行红色部分的查询语句结果是正确的
可是这个触发器不工作,应该是没创建成功,为什么呢?create trigger teaminfotrigger
after insert
on team
for each row
begin
delete from teamsinfo where team_no like '%'
end
DELIMITER $$
begin
insert into teamsinfo
select team_no,MAX(CASE WHEN k.rownum= 2 THEN id
ELSE NULL
END) AS id1,
MAX(CASE WHEN k.rownum = 0 THEN id
ELSE NULL
END) AS id2,
MAX(CASE WHEN k.rownum = 1 THEN id
ELSE NULL
END) AS id3,
university_no as school,
`team_name` AS `team_name`,
`teacher` AS `teacher`,
`teacher_phone` AS `teacher_phone`,
`works_name` AS `works_name`,
`types` AS `types`,
`description` AS `description`
from(
SELECT
team_no,
id,
rownum() as rownum,
university_no,
`team_name` ,
`teacher` ,
`teacher_phone` ,
`works_name`,
`types` ,
`description`
FROM view1 sub) k
group by team_no
DELIMITER $$
end;
DELIMITER $$
解决方案 »
- mysql触发器权限设置
- -- 闲的没事儿,献上我的“My SQL 分库备份”脚本 --
- mysql运行一段时间后就必须重新启动服务才能正常运行?
- 求助:从文件导入MYSQL数据库的记录有1689条,再用语句将其导出,却有1760条,是啥原因?
- 求一条sql,语句,高分,ACMAIN_CHM,WWWWA进来帮看一下,谢谢
- 高手请来看看这个问题如何写出效率更高的sql?
- mysql update问题
- MYSQL不支持嵌套查询怎么办呀?
- MY SQL有没有备份语句和恢复语句
- 求助 like 结合substring_index 的使用
- 为什么写了一个按天分区的存储过程到时间不执行
- mysql6.3 data文件夹中没有数据库
begin
delete from teamsinfo where team_no like '%'
end这个直接改成:delete from teamsinfo 不就行了吗