SQL触发器

文章目录
一 、概述
1-1 触发器基本概念
1-2 触发器种类及分类
二、语法 T-SQL
2-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--删除表 两个逻辑表中。

操作    inserted    delete
insert    是    否
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]
1
2
3
4
5
6
2-1-1 AFTER 举例
要求：修改student 表数据，修改之后查询修改后的数据。

创建触发器SQL语句

--创建修改之后的触发器
CREATE TRIGGER trig_student_After
ON student
FOR  UPDATE 
AS 
    PRINT 'THE TRIGGER IS AFTER'
    SELECT * FROM student
1
2
3
4
5
6
7
触发器被触发的SQL语句

update student set ssex='女' where s_id='20070102'
1
执行结果

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    

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
触发器被触发的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)
1
2
3
4
执行结果 – 数据库未存在 学生的学号

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

2-2 创建 DDL触发器
CREATE TRIGGER 触发器名称
 ON { ALL SERVER | DATABASE }
{ FOR | AFTER }
{ 事件类型|事件组}[,...n]
AS
SQL语句[,...n]
1
2
3
4
5
6
2-2-1 AFTER 举例
要求：插入数据库后输入 ‘创建数据库’

CREATE TRIGGER trig_create
ON ALL SERVER
 AFTER CREATE_DATABASE
AS
    PRINT '创建数据库'
1
2
3
4
5
触发器被触发的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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
执行结果

2-3 修改 DML触发器
ALTER TRIGGER 触发器名称
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [INSERT] | [UPDATE] | [DELETE] }
AS
SQL语句[,...n]
1
2
3
4
5
6
修改 DML触发器 T-SQL 语句 ：可以将创建 DML触发器的关键字 CREATE 修改为 ALTER，这里不再举例子。

2-4 修改 DDL触发器
ALTER TRIGGER 触发器名称
 ON { ALL SERVER | DATABASE }
{ FOR | AFTER }
{ 事件类型|事件组}[,...n]
AS
SQL语句[,...n]
1
2
3
4
5
6
修改 DDL触发器 T-SQL 语句 ：可以将创建 DDL 触发器的关键字 CREATE 修改为 ALTER，这里不再举例子。

2-5 删除触发器
DROP TRIGGER 触发器名称[,...n]
1
2-5-1 举例
举例 ：删除trig_student_After触发器

DROP TRIGGER trig_student_After
1
执行结果

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

创建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