SQLServer数据库的触发器——用途和操作指南-猿码集

SQLServer数据库的触发器——用途和操作指南

SQLServer数据库是企业级应用中常用的数据库之一，它的功能强大，而且效率高。其中的触发器特别受到开发人员喜欢，因为它们可以监控数据库中的特定事件，并且执行相应的操作，从而实现自动化任务。本文将围绕SQLServer数据库的触发器展开，介绍它们的用途和操作指南。

一、什么是触发器

在数据库中，触发器是能够自动执行的一段代码，它们与特定的表相关，监控着针对该表的数据操作。一旦指定的事件发生（例如插入、更新、删除记录），触发器就会自动执行相关的操作，譬如向另一个表插入一条记录、发送电子邮件或是记录某些信息等。

SQLServer的触发器可以分为两种类型：

1、行级触发器（For each row trigger）：当对表中的每一行进行插入、更新或删除操作时都会触发它们。

2、语句级触发器（For each statement trigger）：当对表进行插入、更新或删除操作时，无论影响到多少行记录，它们都只会触发一次。

二、触发器的作用

触发器的作用主要体现在以下方面：

1、数据完整性校验：触发器使得开发人员可以在插入、更新或删除记录之前对数据进行校验。

2、自动化任务处理：触发器可以自动执行某些任务，例如把数据记录到另外一个表中、调用存储过程、执行一些业务逻辑以及向员工发送电子邮件等。

3、访问控制：触发器可以用于限制对某些敏感数据的访问，例如某些管理级别的用户能看到的内容与普通用户的不同。

三、触发器的语法

触发器的语法如下：


CREATE TRIGGER trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
[NOT FOR REPLICATION]
AS
{sql_statements}

其中，CREATE TRIGGER用于创建名为trigger_name的触发器，ON指定触发器监控的表名，FOR | AFTER | INSTEAD OF指定触发器应该监控的事件类型，AS指定触发器所在的数据库上下文中执行的SQL语句。

1、创建行级触发器

创建行级触发器的语法如下：


CREATE TRIGGER trigger_name
ON table_name
FOR {INSERT | UPDATE | DELETE}
AS
{sql_statements}

在这个语法中，我们使用了FOR关键词来指定触发器监控的事件类型。在插入记录时，只有INSERT事件才会触发此触发器；在更新记录时，只有UPDATE事件才会触发；在删除记录时，只有DELETE事件才会触发。

2、创建语句级触发器

创建语句级触发器的语法如下：


CREATE TRIGGER trigger_name
ON table_name
AFTER {INSERT | UPDATE | DELETE}
AS
{sql_statements}

这个语法与行级触发器创建语法的区别主要在于，使用AFTER关键词来指定触发器是语句级触发器。

四、使用示例

以下是我们创建触发器的一些示例。

1、应用行级触发器

下面是一个简单的示例，它会在插入记录时向employees表中的其他字段插入员工的生日。

CREATE TRIGGER tr_insert_employee ON employees FOR INSERT AS UPDATE employees SET birthdate = CONVERT(DATE,'1980-01-01') FROM employees INNER JOIN inserted ON employees.empid = inserted.empid

在这个示例中，我们为名为employees的表创建了一个行级触发器，用于在插入记录时向birthdate字段添加默认日期。插入操作导致在数据库中插入一条新的记录。通过使用INNER JOIN和inserted虚拟表（在插入语句时自动生成），TRIGGER会更新employees表中的特定列。

2、应用语句级触发器

以下示例演示了如何创建一个语句级触发器，以便在employees表上的任何INSERT、UPDATE、DELETE操作之前，向一个名为log_employees的日志表中记录该操作。


CREATE TRIGGER tr_employee_log
ON employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  DECLARE @action_type VARCHAR(50)
  IF EXISTS(SELECT * FROM inserted)
    IF EXISTS(SELECT * FROM deleted)
      SELECT @action_type = 'UPDATE'
    ELSE
      SELECT @action_type = 'INSERT'
  ELSE
    SELECT @action_type = 'DELETE'
  INSERT INTO log_employees(action_type, log_date) VALUES(@action_type, GETDATE())
END

在这个示例中，我们为名为employees的表创建了一个语句级触发器，它会在每次INSERT、UPDATE、DELETE操作之后，将一个字符串变量@action_type定义为插入、更新或删除操作中的行为类型之一。如果插入或更新之前存在数据，@action_type变量将为UPDATE，否则将为INSERT。如果删除操作时存在时间，@action_type变量将为DELETE。TRIGGER然后在log_employees日志表上执行插入，记录操作类型和时间戳。

五、总结

通过本文，我们了解了SQLServer数据库中的触发器及其用法。触发器提供了几种技术，这些技术可以大大提高数据库的自动化。开发人员可以使用触发器来改善应用程序的安全性和性能，并在数据库表中插入、更新或删除记录时自动执行需要的操作。在设计触发器时，请确保只监控对特定表的值变化，以免影响性能。