想建一个宿舍管理系统,有一张学生表student(snum学号),一张宿舍表dorm(dnum宿舍号),还有一张学生宿舍联系表sdr,希望建立一个触发器,在学生表删除某个学生后根据联系表sdr在关联宿舍的已住人数这个属性上减一,
试了几种写法,但都反映有错误~
1.
create trigger del_stu
after delete on student
for each row
as begin
update dorm set dhavel=dhavel-1
from dorm m,deleted d,sdr s
where d.snum=s.snum and m.dnum=s.dnum
end
2.
CREATE trigger del_stu AFTER DELETE ON student FOR each ROW AS BEGIN UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT m.dnum
FROM dorm m, deleted d, sdr s
WHERE d.snum = s.snum
AND m.dnum = s.dnum )
END
以上都说#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax该怎么改?
试了几种写法,但都反映有错误~
1.
create trigger del_stu
after delete on student
for each row
as begin
update dorm set dhavel=dhavel-1
from dorm m,deleted d,sdr s
where d.snum=s.snum and m.dnum=s.dnum
end
2.
CREATE trigger del_stu AFTER DELETE ON student FOR each ROW AS BEGIN UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT m.dnum
FROM dorm m, deleted d, sdr s
WHERE d.snum = s.snum
AND m.dnum = s.dnum )
END
以上都说#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax该怎么改?
from dorm m,deleted d,sdr s
where d.snum=s.snum and m.dnum=s.dnum上面UPDATE语句你想实现的功能是什么?
create trigger del_stu
after delete on student
for each row
as begin
update dorm m,sdr s
set m.dhavel=m.dhavel-1
where old.snum=s.snum and m.dnum=s.dnum;
end ;
$$
delimiter ;
MYSQL要这么写
update语法也不对 改动看2L写的
想我也认为这个语法有问题,所以我写了第二个写法,就是当删除一个学生时,通过sdr表来找到student学生.snum所对应的dorm宿舍.dnum的已住人数dhavel,然后减一
CREATE trigger del_stu AFTER DELETE ON student FOR each ROW AS BEGIN UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT m.dnum
FROM dorm m, deleted d, sdr s
WHERE d.snum = s.snum
AND m.dnum = s.dnum )
END
这样写的话哪里不行啊?
delimiter $$
CREATE trigger del_stu AFTER DELETE ON student FOR each ROW AS BEGIN UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT m.dnum
FROM dorm m, sdr s
WHERE old.snum = s.snum
AND s.dnum = m.dnum )
END ;
$$
delimiter ;
FOR EACH ROW AS
update dorm SET dhavel = dhavel -1 WHERE dnum=(select dnum from sdr where snum=OLD.snum);
MYSQL的语法与MS SQL略有差异。建议参考一下手册。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
8楼写的我试了,还是出错误了
我故意简化了语句,试着连执行下面的语句都出现错误#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax,都不知道怎么回事,表名和属性没错的
CREATE trigger del_stu AFTER DELETE ON student
FOR EACH ROW AS begin
update dorm SET dhavel = dhavel +1 ;
END ;
你没仔细看吧 仔细看看
完全不一样的。。delimiter $$
create trigger del_stu
after delete on student
for each row
as begin
update dorm m,sdr s
set m.dhavel=m.dhavel-1
where old.snum=s.snum and m.dnum=s.dnum;
end ;
$$
delimiter ;
CREATE trigger del_stu AFTER DELETE ON student
FOR EACH ROW AS
update dorm SET dhavel = dhavel -1 WHERE dnum=(select dnum from sdr where snum=OLD.snum);
SQL 查询: CREATE trigger del_stu AFTER DELETE ON student FOR EACH ROW AS UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT dnum
FROM sdr
WHERE snum = OLD.snum ) ;
end;MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS
update dorm SET dhavel = dhavel -1 WHERE dnum=(select dnum from sdr where sn' at line 2
CREATE trigger del_stu AFTER DELETE ON student
FOR EACH ROW AS begin
update dorm SET dhavel = dhavel +1 ;
END ;我是说连这个语句执行都有问题,莫说我本来的语句了
FOR EACH ROW
UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT dnum FROM sdr WHERE snum = OLD.snum ) ;
end;
#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
建议楼主安装一下在线英文字典,比如金山词霸。
你的这个表上已经存在了一个 AFTER DELETE 触发器了。需要先删除。
CREATE trigger del_stu AFTER DELETE ON student
FOR EACH ROW
UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT m.dnum FROM (select a.dnum,a.snum from sdr a)m WHERE m.snum = OLD.snum ) ;
-- CREATE TABLE `dorm` (
`dnum` varchar(10) NOT NULL,
`dpos` varchar(6) default NULL,
`dcollege` varchar(20) default NULL,
`dsex` varchar(2) default NULL,
`dsort` varchar(2) default NULL,
`dexpend` smallint(2) default NULL,
`dcanl` smallint(2) default NULL,
`dhavel` smallint(2) default NULL,
PRIMARY KEY (`dnum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;表的结构 `sdr`
-- CREATE TABLE `sdr` (
`snum` bigint(8) unsigned NOT NULL,
`dnum` varchar(10) NOT NULL,
`bed` smallint(2) default NULL,
PRIMARY KEY (`dnum`,`snum`),
KEY `snum` (`snum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;表的结构 `student`
-- CREATE TABLE `student` (
`snum` bigint(8) unsigned NOT NULL auto_increment,
`sname` varchar(15) NOT NULL,
`scollege` varchar(20) default NULL,
`smj` varchar(15) default NULL,
`sclass` varchar(10) default NULL,
`ssex` varchar(2) default NULL,
`ssort` varchar(2) default NULL,
`st1` smallint(2) default NULL,
`st2` smallint(2) default NULL,
PRIMARY KEY (`snum`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3209000014 ;限制表 `sdr`
--
ALTER TABLE `sdr`
ADD CONSTRAINT `sdr_ibfk_1` FOREIGN KEY (`dnum`) REFERENCES `dorm` (`dnum`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `sdr_ibfk_2` FOREIGN KEY (`snum`) REFERENCES `student` (`snum`) ON DELETE CASCADE ON UPDATE CASCADE;所涉及的表就在上面~
CREATE trigger del_stu AFTER DELETE ON student
FOR EACH ROW
UPDATE dorm SET dhavel = dhavel -1 WHERE dnum = ( SELECT m.dnum FROM (select a.dnum,a.snum from sdr a)m WHERE m.snum = OLD.snum )所建的.
#8楼 已经把官方手册的网址给过你的了。