sqlserver触发器

为了更好的强制业务规则和保证数据的完整性，sql server为我们提供了两种机制，它们分别是约束(主键约束、外键约束、Not Null约束、唯一约束以及检查约束)和触发器。在触发器中，我们可以查询其他表，也可以包含复杂的Transact-SQL语句，并且可以将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到严重错误（例如，磁盘空间不足），则整个事务即自动回滚
说到这里，我想到了数据库中的另外一个东西——存储过程，它们两个真的是太像了，几乎可以说存储过程可以做什么触发器就可以做什么，所以我们也可以把触发器称作是一种特殊的存储过程，那么，既然它是特殊的存储过程，那它到底特殊在哪里呢？触发器与存储过程最大的不同就是：它是与表事件（insert、delete、update）相关的存储过程，它的执行既不是由程序调用的，也不是由手工调用的，而是由事件来触发的，这就是它的神奇之处，比如当我们对一个表进行操作（insert、delete、update）时就会激活它执行，这就满足了普通存储过程所做不到的一些需求.
触发器与存储过程的区别是运行方式的不同，触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行而存储过程需要用户，应用程序或者触发器来显示地调用并执行
一:触发器的优点
1.触发器是自动的。当对表中的数据做了任何修改之后立即被激活。
2.触发器可以通过数据库中的相关表进行层叠修改。
3.触发器可以强制限制。这些限制比用CHECK约束所定义的更复杂。与CHECK约束不同的是，触发器可以引用其他表中的列。
二:触发器的作用
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性，它能够对数据库中的相关表进行级联修改，提高比CHECK约束更复杂的的数据完整性，并自定义错误消息。触发器的主要作用主要有以下接个方面：
强制数据库间的引用完整性
级联修改数据库中所有相关的表，自动触发其它与之相关的操作
跟踪变化，撤销或回滚违法操作，防止非法修改数据
返回自定义的错误消息，约束无法返回信息，而触发器可以
触发器可以调用更多的存储过程

触发器的分类
sql server包括三种常规类型的触发器：DML数据操纵语言（Data Manipulation Language, DML）触发器、DDL 数据库模式定义语言DDL(Data Definition Language)触发器和登录触发器

（1）DML触发器
当数据库中表里面的数据发生变化时，例如进行insert、update、delete操作时，如果我们对该表创建了对应的触发器，那么对应的触发器在数据发生对应变化的时候就会自动执行。DML触发器的主要作用为：强制执行业务规则，以及扩展sql server的约束，默认值等。因为约束只能约束同一个表中的数据，而我们在触发器中可以执行任意sql语句，当然可以将其他表中想约束的任意字段与本表中相对应的字段联合在一起来约束
DML触发器分为：
1、after触发器（执行对应语句之后触发）：insert触发器、update触发器以及delete触发器——只能定义在表上
2、instead of（执行之前触发）：定义了instead of触发器则表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身——可以在表上定义，也可以在视图上定义

（2）DDL触发器
在sql server 2005中新增了DDL触发器，它主要用于审核与规范对数据库表中表、触发器、视图等结构上的操作，比如在修改表、新增表、创建列、修改列等可以影响数据库结构发生变化的时候执行的，我们主要是用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如可以限制不允许删除某些指定的表等

（3）登录触发器
登录触发器是为了响应Login事件而激发的存储过程，与sql server示例建立用户会话时将引发此事件，登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有信息（例如错误消息和来自print语句的消息）会传送到sql server错误日志。如果身份验证失败，将不再激发登录触发器。

instered表和deleted表
触发器有两个特殊的表：instered表和deleted表，这两张是逻辑表也是虚表。由系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被该触发器应用的表的结构相同，当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据，它们具体存储的数据与对应的表数据操作如下：

表操作	Inserted逻辑表	Deleted逻辑表
新增记录（insert）	存放增加的记录	无
修改记录（update）	存放更新后的记录	存放更新前的记录
删除记录（delete）	无	存放被删除的记录

为什么我们在对表中的数据执行更新记录的时候，instered表和deleted表中都会有数据呢？因为我们在对表中数据进行更新的操作，实际上是先删除这条记录，然后在新增一条记录，因为这样，所以instered表和deleted表中都会有数据


--测试数据，首先我们创建测试数据，共有两张表：课程表（course）与学生表（student），其中学生表中
--的course_id字段为course的外键USE [demo]
-- 创建course表
CREATE TABLE course(course_id varchar(50) PRIMARY KEY,course_name varchar(50)
)
GO
-- 创建student表
CREATE TABLE student(student_id varchar(50) PRIMARY KEY,student_name varchar(50),course_id varchar(50) FOREIGN KEY REFERENCES course(course_id)
)
GO
-- 插入数据
INSERT INTO course (course_id,course_name)
VALUES 
('C001','语文'),
('C002','数学'),
('C003','英语')
GOINSERT INTO student (student_id,student_name,course_id)
VALUES
('S001','Lucy','C001'),
('S002','Jack','C002'),
('S003','Jane','C003'),
('S004','Jameson','C001')
GO

--创建触发器的语法如下：
CREATE TRIGGER [触发器名称] ON [表名称]FOR UPDATE -- 或DELETE、或INSERT
AS --Transact-SQL(业务逻辑代码)

-- 创建studnet表的触发器
CREATE TRIGGER [dbo].[student_delete] ON [dbo].[student]
FOR DELETEASDECLARE @course_id VARCHAR(50)
SELECT @course_id = course_id FROM deleted
IF EXISTS (SELECT 1 FROM student WHERE course_id = @course_id)
BEGINPRINT 'student表中存在学习该课程的学生' 
END
ELSE
BEGINPRINT 'student表中不存在学习该课程的学生' DELETE course where course_id = @course_idPRINT 'course表中相关数据已删除'
END--当学生表（student）中没有学生学习某一门课程的时候，我们将这门课程自动删除。这时，我们就需要
--创建一个delete触发器，代码如下：

--当学生表（student）中没有学生学习某一门课程的时候，我们将这门课程自动删除。这时，我们就需要
--创建一个delete触发器，代码如下：
delete student where student_id = 'S001'
--现在我们对此触发器做一个测试，我们从上面的测试数据知道，Lucy和Jameson都学习了语文这门课程
--（course_id为“C001”），现在Lucy由于转班去了别的班级，我们需要将此学生的信息删除

ALTER TABLE course
ADD course_teacherName varchar(50)
GOUPDATE course SET course_teacherName =
CASE WHEN course_id = 'C002' THEN '邢道荣'
WHEN course_id = 'C003' THEN '潘凤' END
GO
--给student表中添加任课教师列（course_teacherName）
--然后为在表中的数据添加相关任课教师的名称

-- 创建studnet表的触发器
CREATE TRIGGER [dbo].[course_update] ON [dbo].[course]
FOR UPDATE
AS
IF (UPDATE(course_teacherName))
BEGINraiserror('任课老师不允许修改！',16,1);----raiserror 是用于抛出一个错误rollback tran;
END
--课程的任课教师不允许修改，那么我们就需要创建一个对应的update触发器--创建好了之后，我们再来更新任课教师的内容，这个触发器满足了我们的需求 测试代码如下
UPDATE course SET course_teacherName = '许褚' WHERE course_id = 'C002'

insert触发器示例

--insert触发器示例
--需求，我们需要在course表中添加更新时间字段（update_time），当我们插入学生的时候，就要对相应的该---课程的时间进行更新，代码如下：
-- 为course表添加字段
ALTER TABLE course
ADD update_time varchar(50)
GO
-- 创建student表的触发器
CREATE TRIGGER [dbo].[course_insert] ON [dbo].[student]
FOR insert
AS
DECLARE @course_id varchar(50)
SELECT @course_id = course_id FROM inserted
UPDATE course SET update_time = CONVERT(VARCHAR(50),GETDATE(),21) WHERE course_id = @course_id
PRINT '相应课程的修改时间已更新'

--修改触发器语法
ALTER TRIGGER [触发器名称] ON [表名称]FOR UPDATE -- 或DELETE、或INSERT
AS --Transact-SQL(业务逻辑代码)--删除触发器语法
drop trigger 触发器名称

--测试一下，为student表中新添加一个学生
INSERT INTO student VALUES ('S004','张三','C003')

其他示例：

USE [PlatformBase]
GO
/****** Object:  Trigger [dbo].[hairpin]    Script Date: 2022/8/11 14:17:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER TRIGGER [dbo].[hairpin]ON   [dbo].[MESGroupProject]AFTER INSERT
AS 
BEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. Declare @name1 nvarchar(500);Declare @name2 nvarchar(500);Declare @id varchar(100);select @name1=TodayContent ,@name2 =TomorrowPlan,@id=ID from inserted;if(CHARINDEX('hairpin',LOWER(@name1))>0 or CHARINDEX('hairpin',LOWER(@name2))>0)Begin if exists (select 1 from MESGroupProject where ID=@id)Begindelete from MESGroupProject where ID=@idendinsert  dbo.MESGroupProject([ID],[Del_Sign] ,ProjectNo,[TodayContent] ,[TodayAttach],[TomorrowPlan] ,[TodayProgress],[Add_User] ,[Add_Date] ,[TimeSpan] ,[MESProjectNo],[GroupID] ,[ProjectDate] ,[IsUpload] ,[Log],[UserCode] , [Dep_Project_No]) 
select [ID],[Del_Sign] ,ProjectNo,REPLACE(LOWER(@name1), 'hairpin',' 项目') ,[TodayAttach],REPLACE(LOWER(@name1), 'hairpin',' 项目'),[TodayProgress],[Add_User] ,[Add_Date] ,[TimeSpan] ,[MESProjectNo],[GroupID] ,[ProjectDate] ,[IsUpload] ,[Log],[UserCode] ,[Dep_Project_No] from insertedendSET NOCOUNT ON;-- Insert statements for trigger here
END