集宁我爱你歌词,马义河,听微微tingvv
触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。
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 触发器。
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 ]
参数介绍:
准备一张订单表(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())
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论