Data Base学习记录：SQL_触发器

为什么用触发器

• 定义： 触发器就是对某一个表的一定的操作触发某种条件， 从而执行的一段程序。触发器是一个特殊的存储过程
• 常见的触发器有三种：分别应用于Insert、Update、Delete事件
• 为什么要使用触发器？
• Student(StudentID,…)
  BorrowRecord(BorrowRecord, StudentID, BorrowDate, ReturnDate )
  –学生借书记录表(流水号,学号,借出时间,归还时间)
  用到的功能有:
  • 如果更改了学生的学号，希望他的借书记录仍然与这个学生相关（也就是同时更改借书记录表的学号）
  • 如果该学生已经毕业，希望删除他的学号的同时，也删除它的借书记录

触发器的建立

CREATE TRIGGER trigger_name	ON { table | view } [ WITH ENCRYPTION ]
{	{ { FOR | AFTER | INSTEAD OF } {[DELETE] [,] [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ] AS
{ IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ] [ ...n ]}
sql_statement [ ...n ]	}	}

触发器的建立-注意事项

• CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中
• 触发器只能在当前的数据库中创建，不过触发器可以引用当前数据库的外部对象
• 如果指定触发器所有者名称以限定触发器，请以相同的方式限定表名
• 在同一条 CREATE TRIGGER 语句中，可以为多种用户操作（如 INSERT 和 UPDATE）定义相同的触发器操作
• 当触发器激发时，将向调用应用程序返回结果。若要避免返回结果，请不要包含返回结果的SELECT 语句，也不要在触发器中进行变量赋值的语句
• 触发器中不允许进行DATABASE的建立、修改和删除操作

触发器的建立-例

Student(StudentID,	)
BorrowRecord(BorrowRecord, StudentID, BorrowDate, ReturnDate )
CREATE TRIGGER truStudent ON Student FOR UPDATE AS
IF Update(StudentID) BEGIN
UPDATE BorrowRecord
SET StudentID=i.StudentID
FROM BorrowRecord br, Deleted d, Inserted i WHERE br.StudentID=d.StudentID
END
Deleted与Inserted分别表示触发事件的表“旧的一条记录” 和“新的一条记录”。
Student(StudentID,	)
BorrowRecord(BorrowRecord, StudentID, BorrowDate, ReturnDate )
CREATE TRIGGER trdStudent ON Student FOR DELETE AS
DELETE BorrowRecord
FROM BorrowRecord br, Deleted d WHERE br.StudentID=d.StudentID

触发器的修改

ALTER TRIGGER trigger_name ON ( table | view ) [ WITH ENCRYPTION ]
{	( FOR | AFTER | INSTEAD OF ) { [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ] AS
{ IF UPDATE ( column )[ { AND | OR }
UPDATE( column ) ] [ ...n ]}
sql_statement [ ...n ] }

触发器的修改-例

CREATE TRIGGER royalty_reminder	ON roysched WITH ENCRYPTION
FOR INSERT, UPDATE
AS RAISERROR (50009, 16, 10)
-- Now, alter the trigger.

ALTER TRIGGER royalty_reminder
INSERT	ON roysched
AS	FOR
RAISERROR (50009, 16, 10)		
--消息 50009 是 sysmessages 中的用户定义消息。

触发器的删除

DROP TRIGGER { trigger } [ ,...n ]
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'employee_insupd' AND type = 'TR')
DROP TRIGGER employee_insupd GO

触发器的建立

• AFTER：指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。默认为AFTER
  • 不能在视图上定义 AFTER 触发器
• INSTEAD OF：指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作
  • 在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个INSTEAD OF 触发器
  • 不能在 WITH CHECK OPTION 的可更新视图上定义INSTEAD OF 触发器
• { [DELETE] [,] [INSERT] [,] [UPDATE] }: 指定在表或视图上执行哪些数据修改语句时将激活触发器。
  • 必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项
• NOT FOR REPLICATION: 当复制进程更改触发器所涉及的表时，不应执行该触发器
• IF UPDATE (column)测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。可以指定多列。因为在 ON 子句中指定了表名，所以在 IF UPDATE 子句中的列名前不要包含表名
• sql_statement：触发器的条件和操作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作
  • 触发器可以包含任意数量和种类的 Transact-SQL 语句
  • 触发器旨在根据数据修改语句检查或更改数据；它不应将数据返回给用户。触发器中的 Transact-SQL 语句常常包含控制流语言
  • CREATE TRIGGER 语句中可使用deleted 和 inserted表，它们是逻辑（概念）表。这些表在结构上类似于定义触发器的表（也就是在其中尝试用户操作的表）；这些表用于保存用户操作可能更改的行的旧值或新值
  • 一个Update 的过程可以看作为：生成新的记录到Inserted 表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录