sql server中的触发器

目录

1.触发器的定义

2 触发器的分类

3 : dml触发器的工作原理

4 触发器的应用

1.insert触发器

2.delete触发器

3 update 触发器

DDL触发器

5.触发器的启用和禁用

1.触发器的定义

触发器其实就是一个特殊的存储过程,这个存储过程呢,不能调用罢了, 而是当数据发生变化的时候才触发了这个过程,;

2 触发器的分类

1) ,ddl触发器, 针对数据库的更新变化

主要是以create,drop,alter开头的语句的触发

2)dml触发器 这个针对表达数据更新

after |for 触发器(动作完成之后触发)

insert 触发器:

delete触发器

update触发器

3)登录触发器

登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

3 : dml触发器的工作原理

在dml触发器执行过程中, 会产生两张临时表 inserted and deleted 在执行完毕后自动释放;

4 触发器的应用

准备数据

工欲善其事,必先利其器,(准备数据):

create table book (bid int primary keynot null ,bname varchar(200)not null , bauther varchar(100) ,bprice decimal(10,2) )insert into book(bid,bname,bprice,bauther)values (1,'论语',25.6 ,'孔子'), (2,'天龙八部',25.6 ,'金庸') , (3,'雪山飞狐',32.7 ,'金庸'), (4,'平凡的世',35.8 ,'路遥' ) , (5,'史记',54.8 ,'司马迁') , (6,'狂人日记',35.5,'鲁迅')

创建触发器的格式:

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION]--给触发器文本加密 FOR|after |instead of[DELETE, INSERT, UPDATE] ---多加一句after和for 是一个功能, 用一个就好了AS T-SQL语句 GO

[WITH ENCRYPTION] –给触发器文本加密

for 和after — 其实就是同一个功能,

instead of 就是执行某个操作之前

1.insert触发器

创建触发器

--判断数据库中,是否存在这个触发器IF (object_id('tr_insert_book','tr') is not null) drop trigger tr_insert_bookgocreate trigger tr_insert_bookon bookinstead ofinsert as begin declare @bid int ;select @bid = bid from inserted ;insert into books(bid) values(@bid);print'插入成功';end

测试:

insert into book values(8,'鹿鼎记','金庸',60)

结果:

2delete触发器

创建触发器:

IF(OBJECT_ID('TR_DELETE_BOOK','TR') IS NOT NULL ) DROP TRIGGER TR_DELETE_BOOKGOCREATE trigger TR_DELETE_BOOKON BOOKFOR DELETE AS BEGINprint'数据备份';if(object_id('book_back','U') is not null ) insert into book_back select * from deleted; else select * into book_back from deleted;print '备份完成'end;

测试:

delete from book where bid =1select * from book_back

少了bid=1 的数据

book_back成功备份;

3 update 触发器

创建触发器

IF(OBJECT_ID('TR_UPDATE_BOOK','TR') IS NOT NULL ) DROP TRIGGER TR_UPDATE_BOOKGOCREATE TRIGGER TR_UPDATE_BOOKON BOOK AFTER UPDATEAS BEGIN waitfor delay '00:00:02'select * from book;END

测试

update book set bname ='金瓶梅' ,bauter='未知',bprice =12345 where bid =8

结果:

补充:

update列级更新

需求, 比如book的bid是主键 ,那就不能更改, 我们应该这么办呢” />IF(OBJECT_ID(‘TR_UPDATE1_BOOK’,’TR’) IS NOT NULL ) DROP TRIGGER TR_UPDATE1_BOOKGOcreate trigger TR_UPDATE1_BOOKon bookinstead of updateas begin if(update(bid))beginprint’主键不能更改!!!!’;rollback; end end

测试:

update book set bid =1 select * from book

DDL触发器

创建触发器

IF(OBJECT_ID('TR_drop_BOOK','TR') IS NOT NULL ) DROP TRIGGER TR_drop_BOOKGOcreate trigger TR_drop_BOOKon database instead of DROP_TABLE,ALTER_TABLEas begin print'别想着干坏事!!!!好好工作'rollback;end 

测试

drop table book

结果

5.触发器的启用和禁用

dml触发器:

enable |disable trigger triger_name on table_name ;

ddl触发器:

enable | disable triggertriger_name on database;