--创建触发器语句
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