概述:
本章我们学习下,数据库高级应用触发器部分的知识!数据库完整性约束包含:实体完整性约束,域完整性约束,参照完整性约束,用户自定义完整性约束!
触发器的作用,主要用于满足参照完整性约束与用户自定义完整性约束!在这些领域的需求,我们都是可以通过触发器来实现,巧用触发器不仅可以提升项目开发效率,还可以满足很多特殊的需求,接下来让我们一探究竟!
什么是触发器?
触发器(Trigger)是一种维护数据完整性约束的手段,它是与表绑定的特殊的存储过程,基于事件的响应模式,符合触发器的设置条件,那么就会执行相应的代码!
触发器的特点是什么?
1:它是一种特殊的存储过程。
2:基于事件的响应模式。
3:具备事务的特性,可以保证数据的一致性,支持数据回顾操作。
4:它是一种高级约束,可以满足特殊的业务规则。
5:触发器的事件响应DML语句,例如:DELETE触发器、INSERT触发器、UPDATE触发器,当触发器绑定表之后,明确设置响应语句类型,那么当该表接受到DML语句中特定的操作,则会做出相应的响应。
触发器的执行步骤是什么?
触发器触发时,系统自动在内存中创建deleted表或inserted表,该表为临时只读类型,不允许修改;触发器执行完成后,则自动删除!
inserted 表 :临时保存了插入或更新后的记录行。deleted 表:临时保存了删除或更新前的记录行。
如何创建触发器?
create trigger Demo_Triger --触发器名字
on A --作用在哪张表中
for insert --针对操作类型
AS
--执行的业务操作脚本
接下来让我们实际操作一下,比如一张员工表有三个字段,工号、名字、性别,性别只能选择男或者女,这个需求属于自定义完整性约束,需要通过触发器来实现!
CREATE TABLE EMPLOYEES
(
ENO INT,
ENAME VARCHAR(20),
ESEX CHAR(2)
)
CREATE trigger EMPLOYEES_SexCheck_Triger
on EMPLOYEES
for insert
AS
DECLARE @SEX CHAR(2)
SELECT @SEX = ESEX FROM inserted --将插入的数据查询出来
PRINT @SEX
IF(@SEX <> '男' AND @SEX <> '女')
BEGIN
PRINT '性别非法!'
ROLLBACK
END
ELSE
BEGIN
PRINT 'No Problem!'
END
INSERT INTO EMPLOYEES SELECT 1,'张三','男';
INSERT INTO EMPLOYEES SELECT 2,'里斯','女';
INSERT INTO EMPLOYEES SELECT 3,'张三','它';
再来一个用触发器满足参照完整性约束的案例,一个员工表,一个工资表,两张表为一对多强耦合关系,员工离职需要删除员工信息,同时将工资信息也同步删除!
CREATE TABLE EMPLOYEES
(
ENO INT,
ENAME VARCHAR(20),
ESEX CHAR(2)
)
CREATE TABLE EMPLOYEES_Salary
(
esid int identity,
ENO INT,
esmoney money,
esTime datetime
)
SELECT * FROM EMPLOYEES AS EM
INNER JOIN EMPLOYEES_Salary AS ES ON EM.ENO = ES.ENO
在员工表上创建删除的触发器:
CREATE trigger EMPLOYEES_DELETE_Triger
on EMPLOYEES
for delete
AS
DECLARE @ENO int
--将保存在deleted临时表中的数据查询出来
SELECT @ENO = ENO FROM deleted
--同步删除子表数据
DELETE FROM EMPLOYEES_Salary WHERE ENO = @ENO
DELETE FROM EMPLOYEES WHERE ENO = 3
接下来综合实战一波,需求每次更新Test表,保存更新前的记录,将记录信息保存到更新日志表Test_Log中,最后将第一次更新的数据,更新回Test表中!
CREATE TABLE Test
(
id int,
name varchar(20)
)
CREATE TABLE Test_log
(
tid int identity,
id int,
name varchar(20),
createtime datetime
)
INSERT INTO TEST SELECT 1,'A'
INSERT INTO TEST SELECT 2,'B'
INSERT INTO TEST SELECT 3,'C'
CREATE trigger Test_Update_Triger
on Test
for update
AS
DECLARE @id int,@name varchar(20)
--将保存在deleted临时表中的数据查询出来
SELECT @id=id ,@name=name FROM deleted
--同步到Test_Log日志表
INSERT INTO Test_log SELECT @id,@name,getdate()
SELECT * FROM Test
UPDATE TEST SET NAME = 'A1' WHERE ID = 1;
UPDATE TEST SET NAME = 'A2' WHERE ID = 1;
UPDATE TEST SET NAME = 'A3' WHERE ID = 1;
UPDATE TEST SET NAME = 'B1' WHERE ID = 2;
UPDATE TEST SET NAME = 'B2' WHERE ID = 2;
UPDATE TEST SET NAME = 'C1' WHERE ID = 3;
UPDATE TEST SET NAME = 'C2' WHERE ID = 3;
UPDATE TEST SET NAME = 'C3' WHERE ID = 3;
UPDATE TEST SET NAME = 'C4' WHERE ID = 3;
SELECT * FROM TEST AS T
INNER JOIN TEST_LOG AS TL ON T.id = TL.id
每次更新都会触发触发器,保存我们的更新日志记录,满足我们对于参照完整性约束的需求,接下来我们通过SQL,让Test表中的数据,重新回到初始状态。
--STEP 1
SELECT MIN(createtime) as createtime FROM Test_log
GROUP BY id
--STEP 2
SELECT id,name,createtime FROM Test_log
WHERE createtime IN
(
SELECT MIN(createtime) as createtime FROM Test_log
GROUP BY id
)
--STEMP 3
UPDATE TEST SET NAME = T2.name FROM TEST AS T1
INNER JOIN
(
SELECT id,name,createtime FROM Test_log
WHERE createtime IN
(
SELECT MIN(createtime) as createtime FROM Test_log
GROUP BY id
)
)AS T2 ON T1.id = T2.id
SELECT * FROM TEST
总结一下:
以上就是关于触发器相关的知识,通过案例的深入,应该可以理解,并且掌握触发器的应用了!