关于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)
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)
...全文
相关推荐
超级经典的SQL题目
此资源包括答案,不过题目真的很经典,但是有点难度。包括索引,视图,触发器等等
SQL-Leetcode-Challenge:包含所有117个Leetcode问题及其解决方案,从MySQL的Easy到Hard
SQL-Leetcode-Challenge:包含所有117个Leetcode问题及其解决方案,从MySQL的Easy到Hard
SQL代码 sql练习
SQL代码 sql练习,很多sql相关的练习,可以参考学习下,对pl/sql有帮助
sql经典代码汇集
精心收集了很多常用的经典的SQL功能代码。绝对的是经典~~~~
SQL Server 触发器 表的特定字段更新时,触发Update触发器
代码如下: create trigger TR_MasterTable_Update on MasterTable after update as if update ([Type])–当Type字段被更新时,才会触发此触发器 insert into MasterLogTable select Id ,(Case [Type] when 1 then ‘Type1’ when 2 then ‘Type2’ when 3 then ‘Type3’ when 4 then ‘Type4’ else ‘TypeDefault’ end) ,Name from inserted go 另外再补充一句


