--创建触发器实现删除主表中的数据时自动删除附表中的数据:--创建表tbl_stu: create table tbl_stu( stuid varchar(15) primary key, stuname varchar(20) not null )--插入数据: insert into tbl_stu values('200911076','李白和') insert into tbl_stu values('200911077','王洪洋')--创建表tbl_score: create table tbl_score( stuid varchar(15) foreign key references tbl_stu(stuid) not null, Math int default(0) )--插入数据: insert into tbl_score values('200911076',85) insert into tbl_score values('200911077',95)--创建视图v_view: create view v_view as select s.stuid,s.stuname,sc.math from tbl_stu s,tbl_score sc where s.stuid=sc.stuid--为视图创建触发器tr_delete: create trigger tr_delete on v_view instead of delete as declare @stuid varchar(15),@stuname varchar(20), @math int select @stuid=stuid,@stuname=stuname,@math=math from deleted delete tbl_score where stuid=@stuid delete tbl_stu where stuid=@stuid--验证是否删除两表中对应的所有信息 delete v_view where stuid='200911077' select *from tbl_stu select *from tbl_score
create table tbl_stu(
stuid varchar(15) primary key,
stuname varchar(20) not null
)--插入数据:
insert into tbl_stu values('200911076','李白和')
insert into tbl_stu values('200911077','王洪洋')--创建表tbl_score:
create table tbl_score(
stuid varchar(15)
foreign key
references tbl_stu(stuid)
not null,
Math int default(0)
)--插入数据:
insert into tbl_score values('200911076',85)
insert into tbl_score values('200911077',95)--创建视图v_view:
create view v_view
as
select
s.stuid,s.stuname,sc.math
from
tbl_stu s,tbl_score sc
where
s.stuid=sc.stuid--为视图创建触发器tr_delete:
create trigger tr_delete on v_view
instead of delete
as
declare @stuid varchar(15),@stuname varchar(20),
@math int
select @stuid=stuid,@stuname=stuname,@math=math
from deleted
delete tbl_score where stuid=@stuid
delete tbl_stu where stuid=@stuid--验证是否删除两表中对应的所有信息
delete v_view where stuid='200911077'
select *from tbl_stu
select *from tbl_score