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

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

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

--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 [dbo].[课程表](

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

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

)

 

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

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

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

    [grade] [nvarchar](50) NULL,

    primary key(studentid,courseid),

)

--删除表：

drop table 学生表;

drop table 课程表;

drop table 学生课程表;

 

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

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

 

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

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

 

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

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

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

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

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

create trigger trg_A

on 学生表

for update,delete

as

begin

if exists(select 1 from inserted)

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

else

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

end

go

--删除操作：

delete from 学生表where studentid='001'

--更新操作

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

 

--查询：

select * from 学生表order by studentid ;

select * from 课程表order by courseid ;

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