SQL SERVER TRIGGER 触发器

1.触发器简介

触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。

2.触发器类型

1、DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生 DML 事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete 操作的语句。

2、DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生 DDL 事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

3、登陆触发器:是指当用户登录 SQL SERVER 实例建立会话时触发。如果身份验证失败,登录触发器不会触发。

其中 DML 触发器比较常用,根据 DML 触发器触发的方式不同又分为以下两种情况:

after 触发器(之后触发):其中 after 触发器要求只有执行 insert、update、delete 某一操作之后触发器才会被触发,且只能定义在表上。

instead of 触发器 (之前触发):instead of 触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。可以在表或视图上定义 instead of 触发器。

3.触发器语法结构

AFTER 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  

<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  

<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  

<method_specifier> ::=  
    assembly_name.class_name.method_name

DDL 触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

登陆触发器语法:

CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  

<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

 参数介绍:

  • CREATE OR ALTER:创建或者有条件的修改触发器(即要修改的触发器必须已经存在)
  • schema_name:ML触发器所属的模式的名称(即所有者,例如:dbo)。
  • trigger_name:触发器的名称
  • table | view:执行 DML 触发器的表或视图,有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由 INSTEAD OF 触发器引用
  • DATABASE:将 DDL 触发器的范围应用于当前数据库。如果指定,触发器会在当前数据库中发生 event_type 或 event_group 时触发。
  • ALL SERVER:将 DDL 或登录触发器的作用域应用于当前服务器。如果指定,触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发
  • WITH ENCRYPTION:加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为 SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。
  • EXECUTE AS:指定执行触发器的安全上下文。以便能够控制 SQL Server 实例用于验证触发器引用的任何数据库对象的权限的用户帐户。
  • NATIVE_COMPILATION:表示触发器是本地编译的。
  • SCHEMABINDING:指定触发器引用的表不能被删除或更改。
  • FOR | AFTER:AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML 触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR 是指定的唯一关键字时,AFTER 是默认值。视图无法定义AFTER触发器。
  • INSTEAD OF:指定执行 DML 触发器而不是触发 SQL 语句,因此覆盖触发语句的操作。无法为 DDL 或登录触发器指定 INSTEAD OF。对于 INSTEAD OF 触发器,在具有指定级联动作 ON DELETE 的引用关系的表上不允许使用 DELETE 选项。类似地,在具有指定级联动作 ON UPDATE 的引用关系的表上,不允许 UPDATE 选项。
  • {[DELETE] [,] [INSERT] [,] [UPDATE]} :指定在针对此表或视图进行尝试时激活 DML 触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。
  • event_type:是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。
  • event_group:是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL 语言事件执行后的 DDL 触发器触发 event_group。
  • sql_statement:是触发条件和动作。触发条件指定附加条件,以确定尝试的 DML,DDL 或登录事件是否导致执行触发器操作。
  • method_specifier:对于 CLR 触发器,指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回 void。class_name 必须是有效的 SQL Server 标识符,并且必须作为具有程序集可见性的程序集中的类存在。

DML触发器例子

准备一张订单表(tbOrder),包含订单ID,订单交易金额,订单创建时间,准备订单交易分段时间总额(tbOrderTotalPrice ),用来统计每个月的交易合计金额

创建INSERT 触发器:当下单后更新tbOrderTotalPrice 的统计信息

USE [TEST]
GO

/****** Object:  Trigger [dbo].[tSumTotalOrderPrice]    Script Date: 2018/5/15 11:10:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tSumTotalOrderPrice] on [dbo].[tbOrder]
AFTER INSERT
AS
BEGIN
	DECLARE @DT INT
	SET @DT= CONVERT(varchar(6),GETDATE(),112)

	DECLARE @SUM DECIMAL(18, 2)
	SET @SUM=(SELECT SUM(OPrice) FROM tbOrder WHERE @DT = CONVERT(varchar(6),CreateDT,112))

	IF(EXISTS(SELECT * FROM tbOrderTotalPrice WHERE YearMoth=@DT))
	BEGIN
		UPDATE tbOrderTotalPrice SET TotalPrice=@SUM WHERE YearMoth=@DT
	END
	ELSE
	BEGIN
		INSERT INTO tbOrderTotalPrice VALUES (@DT,@SUM)
	END
END

GO

执行INSERT 行为:INSERT INTO [dbo].[tbOrder]([OPrice],[CreateDT])VALUES(9,GETDATE())

 

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐