文章目录

一 、概述1-1 触发器基本概念1-2 触发器种类及分类

二、语法 T-SQL2-1 创建 DML触发器2-1-1 AFTER 举例2-1-2 INSTEAT OF 举例

2-2 创建 DDL触发器2-2-1 AFTER 举例

2-3 修改 DML触发器2-4 修改 DDL触发器2-5 删除触发器2-5-1 举例

三、 创建举例用的数据库及表

一 、概述

1-1 触发器基本概念

什么是触发器

触发器是一种特殊的存储过程。但触发器没有输入和输出参数，因而不能被显示调用。它作为语句的执行结果自动引发，而存储过程则是通过存储过程名称被直接调用。

触发器的功能

1. 强化约束

触发器能够实现比 CHECK 语句更为复杂的约束。触发器可以很方便地引用其他表的列，去进行逻辑上的检查。触发器是在 CHECK 之后执行的触发器可以插入、删除、更新多行

2. 跟踪变化

触发器可以检测数据库内的操作，从而禁止数据库中未经许可的更新和变化，以确保输入表中的数据的有效性。 例如：库存系统中，触发器检测到当实际库存下降到需要再进货的临界值时，就给管理员相应提示信息或自动生成给供应商的订单。

3. 级联运行

触发器可以检测数据库内的操作，并自动地级联影响整个数据库的不同表的各项内容。 举例：删除主键的数据，外键表的数据可以修改为NULL或删除该数据。

4. 调用存储过程

为了响应数据库更新，可以调用一个或多个触发器。

1-2 触发器种类及分类

触发器种类

常用 DML触发器，DDL触发器用的相对来说比较少。

DML触发器： 用户通过数据操作语句 DML（对表或视图的insert、delete、update）编辑数据，则执行DML触发器。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误，则整个事务自动回滚。

DDL触发器 为了响应各种数据定义语言 DDL（以CREATE、INSTERT、UPDATE）事件而激发。可以用于在数据库执行管理任务。

触发器分类

一个表可以创建多个 After触发器，但只能创建一个 instead of触发器

after 这类触发器是在记录已经被修改完，事务已提交后被触发执行。主要用记录变更后的处理或检查，一旦发现BUG，可以使用ROLLBACK TRANSACTION语句回滚本次操作。

instead of 这类触发器不去执行其定义的操作（Insert、update、delete），交给触发器执行，触发器检查操作是否正确，若正确则执行操作。这类触发器用来取代原本的操作，在记录变更之前被触发。

触发器中的逻辑（虚拟）表

当表修改时，无论增加、修改、删除，在数据行中的操作，会保存在 inserted--插入表 和 delete--删除表 两个逻辑表中。

操作inserteddeleteinsert是否update修改之后的数据修改之前的数据delete否是

二、语法 T-SQL

2-1 创建 DML触发器

创建 DML触发器 T-SQL语句

CREATE TRIGGER 触发器名称

ON { table | view }

{ FOR | AFTER | INSTEAD OF }

{ [INSERT] | [UPDATE] | [DELETE] }

AS

SQL语句[,...n]

2-1-1 AFTER 举例

要求：修改student 表数据，修改之后查询修改后的数据。

创建触发器SQL语句

--创建修改之后的触发器

CREATE TRIGGER trig_student_After

ON student

FOR UPDATE

AS

PRINT 'THE TRIGGER IS AFTER'

SELECT * FROM student

触发器被触发的SQL语句

update student set ssex='女' where s_id='20070102'

执行结果

2-1-2 INSTEAT OF 举例

要求：插入数据前判断 s_id 是否已经存在，若存在则输出 ‘插入ID已经存在，不允许插入’ +插入学生的学号，并进行回滚数据；反之，进行插入操作，并输出’成功执行’+插入学生的学号。

创建触发器SQL语句

--创建修改之后的触发器

CREATE TRIGGER trig_student_InsteadOF

ON student

Instead OF INSERT

AS

PRINT 'THE TRIGGER IS instead of'

declare @id char(10)

select @id=s_id from inserted

--判断新插入的数据是否存在

IF EXISTS(SELECT s_id FROM student where s_id=@id)

BEGIN

ROLLBACK TRANSACTION

PRINT '插入ID已经存在，不允许插入'+@id

END

ELSE

BEGIN

INSERT INTO student SELECT * FROM inserted

PRINT '成功执行'+@id

END

触发器被触发的SQL语句

INSERT INTO

student ([s_id],[sname],[ssex],[sbirthday],[sdepartment],[smajor],[spoliticalStatus],[photo],[smemo])

VALUES

('20070104',N'张莉',N'女','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)

执行结果 – 数据库未存在 学生的学号 执行结果 – 数据库已存在 学生的学号

2-2 创建 DDL触发器

CREATE TRIGGER 触发器名称

ON { ALL SERVER | DATABASE }

{ FOR | AFTER }

{ 事件类型|事件组}[,...n]

AS

SQL语句[,...n]

2-2-1 AFTER 举例

要求：插入数据库后输入 ‘创建数据库’

CREATE TRIGGER trig_create

ON ALL SERVER

AFTER CREATE_DATABASE

AS

PRINT '创建数据库'

触发器被触发的SQL语句

IF EXISTS(SELECT * FROM sysdatabases WHERE name='StuInfo1')

PRINT 'StuInfo1数据库已存在'

ELSE

BEGIN

--创建数据库

CREATE DATABASE StuInfo1

ON

(

NAME=StuInfo1,

FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo1.mdf',

SIZE=3MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=10%

)

LOG ON

(

NAME=StuInfo1_log,

FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo1_log.ldf',

SIZE=1MB,

MAXSIZE=100MB,

FILEGROWTH=10%

)

END

执行结果

2-3 修改 DML触发器

ALTER TRIGGER 触发器名称

ON { table | view }

{ FOR | AFTER | INSTEAD OF }

{ [INSERT] | [UPDATE] | [DELETE] }

AS

SQL语句[,...n]

修改 DML触发器 T-SQL 语句 ：可以将创建 DML触发器的关键字 CREATE 修改为 ALTER，这里不再举例子。

2-4 修改 DDL触发器

ALTER TRIGGER 触发器名称

ON { ALL SERVER | DATABASE }

{ FOR | AFTER }

{ 事件类型|事件组}[,...n]

AS

SQL语句[,...n]

修改 DDL触发器 T-SQL 语句 ：可以将创建 DDL 触发器的关键字 CREATE 修改为 ALTER，这里不再举例子。

2-5 删除触发器

DROP TRIGGER 触发器名称[,...n]

2-5-1 举例

举例 ：删除trig_student_After触发器

DROP TRIGGER trig_student_After

执行结果

三、 创建举例用的数据库及表

创建触发器举例用的数据

创建StuInfo数据库，若存在则直接使用创建student表，若已存在删除后创建插入数据

可以将下方SQL语句 复制到 SSMS 工具上直接执行，如果有时间，建议自己敲一遍。扩展一点，SQL语句关键字最好使用大写。如果使用小写，数据库会将关键字从小写转换为大写，增加这一过程则性能会降低，所以最好直接使用大写。

use master

go

-- 判断数据库是否已经存在，若存在不删除直接使用，反之新建

IF EXISTS(SELECT * FROM sysdatabases WHERE name='StuInfo')

PRINT 'StuInfo数据库已存在'

ELSE

BEGIN

--创建数据库

CREATE DATABASE StuInfo

ON

(

NAME=StuInfo,

FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo.mdf',

SIZE=3MB,

MAXSIZE=UNLIMITED,

FILEGROWTH=10%

)

LOG ON

(

NAME=StuInfo_log,

FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StuInfo_log.ldf',

SIZE=1MB,

MAXSIZE=100MB,

FILEGROWTH=10%

)

END

GO

USE StuInfo

GO

-- 判断数据表是否已经存在，若存在删除后创建

IF OBJECT_ID(N'StuInfo..student',N'U') IS NOT NULL

DROP TABLE student

CREATE TABLE student

(

[s_id][char](10)NOT NULL,

[sname][nvarchar](5)NULL,

[ssex][nvarchar](1)NULL,

[sbirthday][date]NULL,

[sdepartment][nvarchar](10)NULL,

[smajor][nvarchar](10)NULL,

[spoliticalStatus][nvarchar](4)NULL,

[phoneName][varchar](100)NULL,

[photo][varchar](max)NULL,

[smemo][nvarchar](max)NULL,

CONSTRAINT[PK_student]PRIMARY KEY CLUSTERED

(

[s_id] ASC

))on [PRIMARY]

GO

-- 向数据库添加数据

USE StuInfo

INSERT INTO

student ([s_id],[sname],[ssex],[sbirthday],[sdepartment],[smajor],[spoliticalStatus],[photo],[smemo])

VALUES

('20070101',N'张莉',N'女','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)

,

('20070102',N'张建',N'男','1/30/1998',N'信息工程学院',N'计算机',N'党员',NULL,NULL)

GO