/*用触发器来实现级联更新级联删除*/
--创建学生表,课程表,学生课程表
--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;