表结构:
create table wap_directory(
dir_id int unsigned not null auto_increment primary key,
community_id int unsigned,
name varchar(20),
parent_id int
);这个trigger的功能是当删除一条记录的时候自动删除所有子记录,
sqlserver的写法:
drop trigger Delete_Anthor;
CREATE TRIGGER Delete_Anthor ON wap_directory
FOR DELETE
AS
IF @@ROWCOUNT=0 RETURN DECLARE @t TABLE(dir_id int,Level int)
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.dir_id,@Level
FROM wap_directory a,deleted d
WHERE a.parent_id=d.dir_id
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.dir_id,@Level
FROM wap_directory a,@t b
WHERE a.parent_id=b.dir_id
AND b.Level=@Level-1
END
DELETE a
FROM wap_directory a,@t b
WHERE a.dir_id=b.dir_id
对trigger不熟, 哪位大虾能否帮我把这个trigger改成mysql版本的, 改好后马上结贴,先谢了!
create table wap_directory(
dir_id int unsigned not null auto_increment primary key,
community_id int unsigned,
name varchar(20),
parent_id int
);这个trigger的功能是当删除一条记录的时候自动删除所有子记录,
sqlserver的写法:
drop trigger Delete_Anthor;
CREATE TRIGGER Delete_Anthor ON wap_directory
FOR DELETE
AS
IF @@ROWCOUNT=0 RETURN DECLARE @t TABLE(dir_id int,Level int)
DECLARE @Level int
SET @Level=1
INSERT @t SELECT a.dir_id,@Level
FROM wap_directory a,deleted d
WHERE a.parent_id=d.dir_id
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t SELECT a.dir_id,@Level
FROM wap_directory a,@t b
WHERE a.parent_id=b.dir_id
AND b.Level=@Level-1
END
DELETE a
FROM wap_directory a,@t b
WHERE a.dir_id=b.dir_id
对trigger不熟, 哪位大虾能否帮我把这个trigger改成mysql版本的, 改好后马上结贴,先谢了!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货