关于sql里触发器的“一些”问题
有表student:并插入记录,
create table student
(id int,
name varchar(5))
go
insert into student values(1,'aaa')
insert into student values(2,'bbb')
insert into student values(3,'ccc')
insert into student values(4,'ddd')
insert into student values(5,'dfg')
select * from student
有表score:并插入记录
create table score
(id int,
vc int,
vb int,
sql int)
go
insert into score values(1,100,80,90)
insert into score values(2,100,80,90)
insert into score values(3,100,80,90)
insert into score values(4,100,80,90)
insert into score values(5,52,45,20)
select * from score
2个表没有建立关系,现有基于以上2个表的视图:stu_sre
create view stu_sre
as
select s.id,name,vc,vb,sql from student s,score sc
where s.id=sc.id
go
问题是针对这个视图做的触发器:
问题1:用instead of做的触发器是不是可以把insert和delete和update三种类型(针对视图)都写在一个触发器里?请写个例子!谢谢!
问题2:下面的代码写的是insert类型的触发器,但是只能是插入全部字段的记录才可以,怎样才可以写个我想插入那个字段的记录就插入那个字段的记录?
create trigger in_stu_sre
on stu_sre
instead of insert
as
declare @sid int
declare @sname varchar(4)
declare @svc int
declare @svb int
declare @ssql int
select @sid=id, @sname=name,@svc=vc @svb=vb, @ssql=sql from inserted
insert into student values(@sid,@sname)
insert into score values(@sid,@svc,@svb,@ssql)
go
insert into stu_sre values(7,'xuxu',10,20,30)