sql Server 触发器入门代码~~

--Sql Server
--触发器通过一个事件来触发，运行
--支持三种触发器，插入，更新，删除
--两个虚拟的测试表 inserted，deleted

--插入的触发器 使用 inserted表
--删除的触发器 使用 deleted表
--更新的触发器 使用 两张表
use study

--创建表member
create table member
(
 m_no varchar(20) primary key ,
 m_name varchar(30)
)
--insert触发器
--判断是不是97级的
alter trigger S_insert
on member
for insert
as
declare @m_no varchar(20)

select @m_no=m_no from inserted
if (left(@m_no,2)!='97')
 begin
 rollback tran
 raiserror('输入的学号d%不是97级的学生',16,1,@m_no)
end

insert into member values('123','way')

insert into member values('9701','way')
insert into member values('9702','way')
insert into member values('9703','way')

--删除触发器
--当删除的记录数大于一行时 取消删除
create trigger S_delete
on member
for delete
as
declare @rowcount int
select @rowcount=@@rowcount
if @rowcount>1
begin
 rollback tran
 raiserror('当前要删除的记录数为d%,一次只允许删除一条记录',16,1,@rowcount)
end

delete from member where m_name='way'
--演示delete触发器的备份作用
create trigger S_update
on member
for update
as
declare @rowcount int
select @rowcount=@@rowcount
if (@rowcount)

--instead of 触发器
--测试如何通过视图向多表插入数据
--stu97
--stu98
--stu99

create view Stu_View
as
select * from stu97
union all
select * from stu98
union all
select * from stu99

create trigger s_instead
on stu_view
instead of insert as
begin
 declare @s_no varchar(20)
end

alter proc GetAllStudent
as