触发器的使用

--创建触发器语句

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO
--with encryption 表示加密触发器定义的sql文本
--delete,insert,update指定触发器的类型

--创建学生表
create table student(
    stu_id int identity(1,1) primary key,
    stu_name varchar(10),
    stu_gender char(2),
    stu_age int
)
--创建insert触发器
create trigger trig_insert
on student
after insert
as
begin
    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在
        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表
    declare @stuNumber int;
    select @stuNumber = count(*)from student;
    if not exists (select * from student_sum)--判断表中是否有记录
        insert into student_sum values(0);
    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
end
--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount
--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;
insert into student(stu_name,stu_gender,stu_age)values('张三','男',30);
select stuCount 学生总人数 from student_sum;    
insert into student(stu_name,stu_gender,stu_age)values('李四','女',30);            
select stuCount 学生总人数 from student_sum;
insert into student(stu_name,stu_gender,stu_age)values('钱五','男',40);                
select stuCount 学生总人数 from student_sum;
--创建insert_forbidden,禁止用户向student_sum表中插入数据
create trigger insert_forbidden
on student_sum
after insert
as
begin
    RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误
rollback transaction
end 
--触发触发器insert_forbidden
insert student_sum (stuCount) values(5);
--创建delete触发器
create trigger trig_delete
on student 
after delete
as
begin
    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age
    from deleted
end;
--执行一一条delete语句触发trig_delete触发器
delete from student where stu_id=1;
--创建update触发器
create trigger trig_update
on student
after update
as
begin
    declare @stuCount int;
    select @stuCount=count(*) from student;
    update student_sum set stuCount =@stuCount;
    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted
    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted
end
--创建完成，执行一条update语句触发trig_update触发器
update student set stu_name='赵六' where stu_id=2;
--创建instead of 触发器 
create trigger trig_insteadOf
on student 
instead of insert
as 
begin
    declare @stuAge int;
    select @stuAge=(select stu_age from inserted)
if(@stuAge >120)
    select '年龄更新错误' as '错误原因'
end
---------------------------------------------------------------------------------------------------
--查看数据库中所有的触发器
use 数据库名
go
select * from sysobjects where xtype='TR'

--触发器修改之前的数据
select * from deleted
--触发器修改之后的数据
select * from inserted