mysql触发器更新级联删除_用触发器来实现级联更新级联删除

/*用触发器来实现级联更新级联删除*/

--创建学生表，课程表，学生课程表

--http://www.yaosansi.com/post/692.html

范本：

触发器方式：

create trigger trg_A

on A

for update,delete

as

begin

if exists(select 1 from inserted)

update B set Name=(select Name from inserted) where Name=(select Name from deleted)

else

delete B where Name=(select Name from deleted)

end

go

下面是一个实例：

CREATE TABLE [dbo].[学生表](

[studentid] [nvarchar](50) primary key NOT NULL,

[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

)

CREATE TABLE [d

/*用触发器来实现级联更新级联删除*/

--创建学生表，课程表，学生课程表

--http://www.yaosansi.com/post/692.html

范本：

触发器方式：

createtrigger trg_A

onA

forupdate,delete

as

begin

ifexists(select 1 from inserted)

updateB set Name=(select Name from inserted) where Name=(select Name from deleted)

else

deleteB where Name=(select Name from deleted)

end

go

下面是一个实例：

CREATETABLE [dbo].[学生表](

[studentid] [nvarchar](50) primary key NOT NULL,

[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

)

CREATETABLE [dbo].[课程表](

[courseid] [nvarchar](50) primary key NOT NULL,

[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

)

CREATETABLE [dbo].[学生课程表](

[studentid] [nvarchar](50) NOT NULL ,

[courseid] [nvarchar](50) NOT NULL ,

[grade] [nvarchar](50) NULL,

primary key(studentid,courseid),

)

--删除表：

droptable学生表;

droptable课程表;

droptable学生课程表;

insertinto学生表values('001','张三');

insertinto学生表values('002','李四');

insertinto课程表values('101','语文');

insertinto课程表values('102','数学');

insertinto学生课程表values('001','101','63');

insertinto学生课程表values('001','102','50');

insertinto学生课程表values('002','101','73');

insertinto学生课程表values('002','102','70');

--创建一个触发器来实现级联更新级联删除

createtrigger trg_A

on 学生表

forupdate,delete

as

begin

ifexists(select 1 from inserted)

update 学生课程表setstudentid=(select studentid from inserted) where studentid=(select studentid from deleted)

else

delete 学生课程表wherestudentid=(select studentid from deleted)

end

go

--删除操作：

deletefrom学生表wherestudentid='001'

--更新操作

update 学生表setstudentid='0001' where studentid='001'

--查询：

select* from学生表orderby studentid ;

select* from课程表orderby courseid ;

select* from学生课程表orderby studentid, courseid;