SQL Server触发器介绍及使用

侠客 2020-12-25

触发器

概述

触发器为特殊类型的存储历程，可在履行说话事故时自动生效。SQL Server 包括三种老例类型的触发器：DML 触发器、DDL 触发器和登录触发器。

当办事器或数据库中发生数据定义说话 (DDL) 事故时将调用 DDL 触发器。登录触发器将为相应 LOGON 事故而引发存储历程。与 SQL Server 实例建立用户会话时将激发此事故。

当数据库中发生数据操作说话 (DML) 事故时将调用 DML 触发器。DML 事故包括在指定表或视图中改动数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包孕繁杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。假如检测履新错（例如，磁盘空间不够），则全部事务即自动回滚。

步骤

本文主要讲述DML触发器，DML触发器有两种：AFTER，INSTEAD OF触发器，同时DML 触发器应用 deleted 和 inserted 逻辑（观点）表。它们在布局上类似于定义了触发器的表，即对其考试测验履行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户变动的行的旧值或新值。

对付INSERT 操作，inserted保留新增的记录，deleted无记录

对付DELETE 操作，inserted无记录，deleted保留被删除的记录

对付UPDATE操作，inserted保留改动后的记录，deleted保留改动前的记录

一.语法

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }[ WITH[ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

AS { sql_statement[ ; ] [ ,...n ] [ ; ] > }

二.创建表

CREATE TABLE Class

(Cno INT PRIMARY KEY,Cname nvarchar(20) not null)

goCREATE TABLE Student

(SNO INT PRIMARY KEY IDENTITY(1,1),Sname CHAR(10) not null,

Age int not null,Sex char(2) not null,

Cno int NOT NULL )

ALTER TABLE Student ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class(Cno)go

AFTER触发器

AFTER 指定 DML 触发器仅在触发 SQL 语句中指定的所有操作都已成功履行时才被触发。所有的引用级联操作和约束反省也必须在引发此触发器之前成功完成。

假如仅指定 FOR 关键字，则 AFTER 为默认值。

不能对视图定义 AFTER 触发器

insert触发

当向Class表中插入一条数据时，获取插入的cno，同时向Student表中插入一条数据

IF OBJECT_ID('TR_Class_insert','TR') IS NOT NULL

DROP TRIGGER TR_Class_insertG0

CREATE TRIGGER TR_Class_insert on ClassAFTER INSERT

AS BEGIN

DECLARE @Cno INTSELECT @Cno=Cno FROM inserted----获取插入的数据CNO

INSERT INTO Student(Sname,Age,Sex,Cno)VALUES('李明',20,'男',@Cno)

END

goINSERT INTO Class

SELECT 101,'一班'

SELECT * FROM ClassSELECT * FROM Student

UPDATE触发

获取改动的Age值，假如Age为负数则履行回滚操作，否则输出改动前后的Age值

IF OBJECT_ID('TR_Student_update','TR') IS NOT NULL

DROP TRIGGER TR_Student_updateGO

CREATE TRIGGER TR_Student_update on StudentAFTER UPDATE

AS BEGIN

DECLARE @Age_old int,@Age_new intSELECT @Age_old=Age from deleted ----获取改动前的

SELECT @Age_new=Age FROM inserted----获取变动后的数据if @Age_new

beginprint '年岁不能为负数'

rollback;end

elseBEGIN

print @Age_oldprint @Age_new

END END

go

update Studentset Age=-20

where SNO=1

SELECT * FROM ClassSELECT * FROM Student

update Student

set Age=25where SNO=1

SELECT * FROM ClassSELECT * FROM Student

delete触发

获取被删除的数据，返回差错提示，该步骤恰恰验证了“所有的引用级联操作和约束反省也必须在引发此触发器之前成功完成”，该步骤不会返回拟订的差错提示，由于被删除的数据感化于外键约束，以是先于触发器操作履行外键约束，返回约束差错提示，并履行回滚.

IF OBJECT_ID('TR_Class_delete','TR') IS NOT NULL

DROP TRIGGER TR_Class_deleteGO

CREATE TRIGGER TR_Class_delete on ClassAFTER DELETE

AS BEGIN

DECLARE @Cno intSELECT @Cno=Cno from DELETED---获取被删除的记录

IF @Cno>0beginRAISERROR ('数据不能被删除，被用于外键约束', 16, 10);

rollback----履行回滚操作end

END

SELECT * FROM ClassSELECT * FROM Student

DELETE FROM Class

where CNO=101

SELECT * FROM ClassSELECT * FROM Student

对Student表建立外键约束，用于级联操作 ON DELETE，对付表的级联删除更新操作这里就不讲述了

删除之前创建的外键约束,并创建具有级联更新删除操作的外键约束

alter table studentdrop constraint FK_SNO_Cno

ALTER TABLE StudentADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)

ON DELETE CASCADE ON UPDATE CASCADE

再履行删除语句，返回拟订差错提示“数据不能被删除，被用于外键约束”并履行回滚操作DELETE FROM Class

where CNO=101

SELECT * FROM ClassSELECT * FROM Student

INSTEAD OF触发器

instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements." data-guid="8ebeb517a8276401c8c46d9d3deae89c">指定履行 DML 触发器而不是触发 SQL 语句，是以，其优先级高于触发语句的操作。不能为 DDL 或登录触发器指定 INSTEAD OF。

对付表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器。然则，可以为具有自己的 INSTEAD OF 触发器的多个视图定义视图。

INSTEAD OF 触发器弗成以用于应用 WITH CHECK OPTION 的可更新视图。假如将 INSTEAD OF 触发器添加到指定了 WITH CHECK OPTION 的可更新视图中，则 SQL Server 将激发差错。用户须用 ALTER VIEW 删除该选项后才能定义 INSTEAD OF 触发器

对付 INSTEAD OF 触发器，不容许对具有指定级联操作 ON DELETE 的引用关系的表应用 DELETE 选项。同样，也不容许对具有指定级联操作 ON UPDATE 的引用关系的表应用 UPDATE 选项

Insert 触发

-------insert 触发

----删除已有的instead of触发器declare @name nvarchar(100)

select @name=name from sys.triggers whereobject_name(parent_id)='student' and is_instead_of_trigger=1set @name='drop trigger '+@name

exec (@name)

IF OBJECT_ID('TR_Student_instead_insert','TR') IS NOT NULL DROP TRIGGER TR_Student_instead_insert

GOCREATE TRIGGER TR_Student_instead_insert on Student

INSTEAD OF insert AS

BEGIN

SELECT * into T_back from inserted ----获取即将插入的数据

END

select * from Studentselect * from Class

INSERT INTO Student(Sname,Age,Sex,Cno)

values('张三',23,'男',102)

select * from T_back

delete触发

创建触发器掉败，由于之前创建外键约束时添加了on delete cascade

IF OBJECT_ID('TR_Student_instead_delete','TR') IS NOT NULL

DROP TRIGGER TR_Student_instead_deleteGO

CREATE TRIGGER TR_Student_instead_delete on StudentINSTEAD OF DELETE

AS BEGIN

DECLARE @Cno intSELECT @Cno=Cno from DELETED---获取被删除的记录

IF EXISTS (SELECT * FROM Class where Cno=@cno)begin

rollback----履行回滚操作RAISERROR ('数据不能被删除，被用于外键约束1', 16, 10);

end

END

消息 2113，级别 16，状态 1，历程 TR_Student_instead_delete，第 10 行由于表 'Student' 的 FOREIGN KEY 应用级联 DELETE 或 UPDATE，以是无法对该表创建 INSTEAD OF DELETE 或 INSTEAD OF UPDATE TRIGGER 'TR_Student_instead_delete'。

重修外键约束，删除级联

alter table studentdrop constraint FK_SNO_Cno

ALTER TABLE Student

ADD CONSTRAINT FK_SNO_Cno FOREIGN KEY (Cno) REFERENCES Class (Cno)

UPDATE触发

-----同一张表中只能定义一个instead of 触发器，删除表之前创建的instead of 触发

declare @name nvarchar(100)

select @name=name from sys.triggers whereobject_name(parent_id)='student' and is_instead_of_trigger=1set @name='drop trigger '+@name

exec (@name)

IF OBJECT_ID('TR_Student_instead_update','TR') IS NOT NULL DROP TRIGGER TR_Student_instead_update

GOCREATE TRIGGER TR_Student_instead_update on Student

INSTEAD OF update AS

BEGINDECLARE @Age_del int ,@Age_up int

SELECT @Age_del=Age from DELETED---获取被变动的记录SELECT @Age_up=Age from Inserted

beginprint @Age_del

print @Age_upselect * from Student ----查询数据是否被变动

end

END

----查询更新前的表数据select * from student

SNOSnameAgeSexCno

李明22男101

update Studentset age=-2

where CNO=101

----对付前面定义的after触发器age不能为负数也不会履行，instead of 触发器高于履行语句，高于after 触发

SNOSnameAgeSexCno

李明22男101

select * from student

SNOSnameAgeSexCno李明22男101

(1 行受影响)

-2

(1 行受影响)

当表上面定义了instead of 触发器，指定履行 DML 触发器而不是触发 SQL 语句，是以，其优先级高于触发语句的操作，而且也不会履行表上面定义的after触发器

创建带字段判断的触发器，根据对特定列的 UPDATE 或 INSERT 改动来履行某些操作

------创建字段更新判断的update触发器

ALTER TABLE ClassADD Address nvarchar(50)

IF OBJECT_ID('TR_Class_Update','TR') IS NOT NULL

DROP TRIGGER TR_Class_UpdateGO

CREATE TRIGGER TR_Class_Update on ClassAFTER UPDATE

AS BEGIN

IF UPDATE(Cname) or UPDATE(Address)BEGIN

RAISERROR ('数据不能被改动', 16, 10)ROLLBACK

END END

SELECT * FROM Class

UPDATE Class

set Address='5栋101' where Cno=101

SELECT * FROM Class

总结

虽然触发器功能强大年夜，轻松靠得住地实现许多繁杂的功能，同时过多触发器会造成数据库及利用法度榜样的掩护艰苦，同时对触发器过分的依附，势必影响数据库的布局，同时增添了掩护的繁杂法度榜样.

上一篇：PS影楼后期调色教程之调出高质感古铜色

下一篇：qq邮箱怎设置新邮件不提醒?

SQL Server触发器介绍及使用

您可能还会对下面的文章感兴趣：

相关文章