sql触发器有哪几类，2大触发器类型及应用实例

威哥 2023-01-06 18:20:03 496

sql触发器有哪几类，2大触发器类型及应用实例3. 登录触发器DDL事件即指在表或索引中的create、alter、drop语句。DML触发器包含两种触发器：2. 数据定义语言 (DDL) 触发器DDL Trigger 是指当服务器或数据库中发生DDL事件时所触发的操作。

sql触发器有哪几类，2大触发器类型及应用实例(1)

触发器复杂语法：

create trigger trigger_name on { table | view } [ with encryption ] --用于加密触发器 { { { for | after | instead of } { [ insert ] [ ] [ update ] } [ with append] [ not for replication] as [ { if update( column ) [ { and | or } update ( column ) ] [ ...n ] | IF ( columns_update ( ) { bitwise_operator } updated_bitmask ) { comparison_operator } column_bitmask [ ...n ] } ] sql_statement [ ...n ] } }

注：

触发器最多可以嵌套 32 层。
for和after同义。
delete 触发器不能捕获truncate table 语句
触发器中不允许以下 Transact-SQL 语句： alter /create/drop/load database 、disk init/resize、 load log 、reconfigure 、restore database/log 等。
触发器也可以根据数据修改前后的表状态，再行采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

sql触发器有哪几类，2大触发器类型及应用实例(2)

SQL Trigger 触发器的分类

1.数据操作语言 (DML) 触发器

DML Trigger 是指触发器在数据库中发生DML事件时所触发的操作。

DML事件即指在表或视图中修改数据的insert、update、delete语句。

DML触发器包含两种触发器：

after触发器：执行DML事件后触发，只能定义在表上。
instead of 触发器：不执行DML事件，仅执行触发器本身。

2. 数据定义语言 (DDL) 触发器

DDL Trigger 是指当服务器或数据库中发生DDL事件时所触发的操作。

DDL事件即指在表或索引中的create、alter、drop语句。

3. 登录触发器

登录触发器一般应用在备份登录或注册信息。

DML触发器的实现使用两个逻辑表（虚表）：deleted表和inserted表

1.两表位置：

deleted表和inserted表，建立在数据库服务器的内存中。

2.两表权限：

只读权限。

3.表结构：

和触发器所在的数据表的结构一样。

4.表作用：

deleted表

用于存放你在操作 insert、update、delete语句前，你创建触发器表中数据库。

2. inserted表

用于存放你在操件insert、update、delete语句后，更新的记录。

5.表时效：

当触发器执行完成后，它们也就会被自动删除。

SQL 触发器查看

查看单个触发器

exec sp_helptext 'TriggerName'

2.查看所有触发器

select * from sysobjects where xtype='TR' 慎用触发器

过多的触发器使得数据逻辑变得复杂
数据操作比较隐含，不易进行调整修改
触发器的功能逐渐在代码逻辑或事务中替代实现，更符合OOP思想。

sql触发器有哪几类，2大触发器类型及应用实例(3)

触发器应用实例

1.insert trigger

插入数据，不满足要求抛出错误，事务回滚

----trigger constraint --判断 if object_id(N'newtable1Insert' N'tr') is not null drop trigger newtable1Insert go --create insert trigger create trigger newtable1Insert on newtable1 for insert as declare @Cid int select @Cid = newtable1.Cid from newtable1 inner join inserted on newtable1.Cid=inserted.Cid print @Cid if(@Cid>43) begin raiserror('班级编号不能超过40个！' 16 8) rollback tran end go insert into newtable1 values (40 '028班') ---执行

结果：参见创建触发器。

2.update trigger

newtable表插入数据时 Classes表同步插入数据。

use TestSchool if object_id('Tr_Classes' 'Tr') is not null drop Trigger Tr_Classes go Create trigger Tr_Classes on newtable for update as begin update Classes set Classes.CName =newtable.CName from Classes newtable where Classes.Cid =newtable.Cid end go

执行更新语句，验证触发器

select * from newtable select * from Classes update newtable set CName='023班' where Cid = 10 select * from newtable select * from Classes

结果展示：

原数据：

sql触发器有哪几类，2大触发器类型及应用实例(4)

更新，触发触发器后结果：

sql触发器有哪几类，2大触发器类型及应用实例(5)

3.delete trigger

如果newtable

use TestSchool if object_id('Tr_Classes_Delete' 'Tr') is not null drop Trigger Tr_Classes_Delete go Create Trigger Tr_Classes_Delete on newtable for delete as begin delete from Classes where Classes.Cid in(select Cid from deleted ) end

结果：

select * from newtable select * from Classes

原表：

sql触发器有哪几类，2大触发器类型及应用实例(6)