Transact-SQL 示例 - 触发器的基础及应用

阅读提示:本篇文章内所使用到的数据库为AdventureWorks OLTP

I:触发器介绍

1.在开始介绍触发器之前我们先回顾一下T-SQL的专业词汇DDL, DML

词汇	中文解释	英文
DDL	数据定义语言	data definition language
DML	数据操作语言	data manipulation language

DDL:定义数据库,表,视图及其他对象的命令语句如:create database, create table, create view, create xxx, alter xxx, drop xxx等

DML:对数据库内的表或视图进行添加,修改,删除操作的命令语句如:insert, update, delete.在这里注意一点,select是属于DQL(Data Query Language, 数据查询语言)而非DML.

备注:以上知识点均参照百度百科的介绍.DML DDL

2.什么是触发器?它的用途是什么?

触发器主要用于帮助SQL Server实现数据完整性约束.当用户对现有数据库的架构(schema)及表数据(包括视图数据)进行相关操作的时候,你可以通过添加触发器去约束用户的操作行为.如果约束验证成功,那么用户的操作将会提交到数据库当中.如果约束验证失败,你可以选择回滚,并将失败的原因告知给用户,然后还可以把本次操作失败的原因记录到日志系统里面,以便为日后的分析提供一个参考依据!

在这里列举一个AdventureWorks数据库的一个禁止删除[HumanResources].[Employee]表数据的例子:

CREATE TRIGGER [HumanResources].[dEmployee] 
   ON [HumanResources].[Employee] 
   INSTEAD OF DELETE --优先于DML触发器执行
   NOT FOR REPLICATION --在复制代理执行插入、更新或删除操作时，不进行触发器约束
AS 
BEGIN
   DECLARE @Count int;
   SET @Count = @@ROWCOUNT;
   IF @Count = 0 
      RETURN;
   SET NOCOUNT ON;
   BEGIN
      RAISERROR(
         N'Employees cannot be deleted. They can only be marked as not current.', -- Message
         10, -- Severity.
         1 -- State.
      ); 
      IF @@TRANCOUNT > 0
      BEGIN
         ROLLBACK TRANSACTION;
      END
   END;
END;

INSTEAD OF DELETE触发器运行效果:

解释一下ROLLBACK TRANSACTION的作用,如果我把
BEGIN
ROLLBACK TRANSACTION;
END
注视掉的话.~会出现以下的情况

可以看到.~虽然显示了DELETE受影响的行数,但是我后面的一个SELECT COUNT(*)显示数据表内的记录仍然存在,并没有被删除.所以加上这三行制造一个伪错误提示给客户端是一个正确的决定.

II.如何使用触发器

1.触发器的定义
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
assembly_name.class_name.method_name

注意:如果同时定义了INSTEAD OF { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 和 FOR | AFTER { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }的话你必须要在INSTEAD OF内使用DML语句才会使FOR|AFTER触发器生效

触发器内可以已INSERTED, DELETED这两个表名去访问将要插入,或被删除的数据信息

另外:不能为 DDL 或登录触发器指定 INSTEAD OF。对于表或视图，每个 INSERT、UPDATE 或 DELETE 语句最多可定义一个 INSTEAD OF 触发器

更多详细资料可以参阅: Technet

2.触发器的种类
A:DML触发器

功能:可以对insert, update, delete语句进行触发检查按照一定条件执行回滚操作,避免不符合业务要求的数据进入到数据表当中,使用INSERTED, DELETED去访问将要被添加,删除的数据.

例2.1-禁止删除DatabaseLog表中的记录

CREATE TRIGGER dDatabaseLog
   ON DatabaseLog
   FOR DELETE
AS
BEGIN
   RAISERROR('不允许删除DatabaseLog表内的数据', 11, 1)
   ROLLBACK
END

执行效果

B:DDL触发器

功能:可以对任何DDL语句进行触发检测并按照设定好的规则进行回滚操作.

例2.2-禁止当前数据库执行DROP TABLE, ALTER TABLE:

CREATE TRIGGER [safety]
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS
BEGIN
   RAISERROR('目前数据库已禁止删除,修改表', 11, 1)
   ROLLBACK;
END

最终效果

3.INSTEAD OF 与 FOR|AFTER触发器

例3-1:

先定义一个不允许删除DatabaseLog.DatabaseLogID < 1500的INSTEAD OF DELETE触发器,然后在定义一个不允许删除DatabaseLog.DatabaseLogID < 1700的FOR DELETE触发器然后看他们被触发的情况.请注意 [DELETED] 的位置

注意:先把之前定义的FOR DELETE触发器dDatabaseLog删掉. DROP TRIGGER dDatabaseLog

CREATE TRIGGER [dbo].[dDatabaseLog_1]
   ON [dbo].[DatabaseLog]
   INSTEAD OF DELETE
AS
BEGIN
   IF EXISTS(
         SELECT * FROM DELETED WHERE DatabaseLogID < 1500
      )
   BEGIN
      --这里并不会调用FOR DELETE触发器,因为这个触发器是INSTEAD OF
      RAISERROR('INSTEAD OF触发器禁止删除DatabaseLogID < 1500的记录', 11, 1)
      IF @@TRANCOUNT > 0
      BEGIN
         ROLLBACK TRANSACTION;
      END
   END
   ELSE
   BEGIN
      --这里开始才调用FOR DELETE触发器
      DELETE FROM DatabaseLog WHERE DatabaseLogID IN (
         SELECT DatabaseLogID FROM DELETED
      )
   END
END

CREATE TRIGGER [dbo].[dDatabaseLog_2]
   ON [dbo].[DatabaseLog]
   FOR DELETE
AS
BEGIN
   --如果进入到这里的话,已证明INSTEAD OF DELETE触发器已通过约束检测
   IF EXISTS(
         SELECT * FROM DELETED WHERE DatabaseLogID < 1700
      )
   BEGIN 
      RAISERROR(
         'FOR触发器禁止删除DatabaseLogID < 1700的记录',
         11, 1
      )
      --此处的回滚操作是由FOR DELETE发出
      ROLLBACK
   END
END

从上述脚本我们可以看到在INSTEAD OF DELETE触发器中并不需要去调用ROLLBACK去回滚操作,因为这是INSTEAD OF触发器的一个特性.

TechNet上介绍INSTEAD OF触发器的定义为:INSTEAD OF 指定执行 DML 触发器而不是触发 SQL 语句，因此，其优先级高于触发语句的操作.

上述示例的运行效果

当DatabaseLogID < 1500条件成立时,INSTEAD OF DELETE没有调用任何DELETE FROM语句,所以FOR DELETE触发器不会触发.

而当我删除DatabaseLogID > 1501的记录时,INSTEAD OF DELETE触发器已经通过检测,然后去提交DELETE FROM去调用FOR DELETE触发器.

最终FOR DELETE因满足约束条件DatabaseLogID < 1700而回滚了删除操作.

III:查询一个数据库内的到底定义了多少个触发器?他们所属的表对象是谁?

1.下面我将会贴出一段T-SQL示例脚本,它的功能为显示出当前数据库内所有的触发器信息

--切换数据库
--use AdventureWorks

WITH cte_AllTriggers AS (
   SELECT 
      o.name TableName,          --触发器所属的对象(表 | 视图)
      o.type_desc TableType,     --描述此触发器所属的对象是表还是视图
      t.name TriggerName,        --触发器的名称
      t.object_id,               --触发器的object_id
      t.parent_class_desc,       --触发器是DML触发器还是DDL触发器
      t.type_desc,               --触发器是SQL触发器还是CLR触发器
      t.create_date,             --触发器的创建日期
      t.modify_date,             --上次使用 ALTER 语句修改触发器的日期
      t.is_ms_shipped,           --是否为内部 SQL Server 组件代表用户创建的触发器
      t.is_disabled,             --触发器是否被禁用
      t.is_not_for_replication,  --触发器是否作为 NOT FOR REPLICATION 创建的
      t.is_instead_of_trigger    --1 = INSTEAD OF 触发器, 0 = AFTER 触发器
   FROM 
      sys.triggers t INNER JOIN sys.objects o 
         ON t.parent_id = o.object_id 
   UNION ALL
   SELECT 
      NULL TableName,          
      NULL TableType,     
      t.name TriggerName,        
      t.object_id,               
      t.parent_class_desc,       
      t.type_desc,               
      t.create_date,             
      t.modify_date,             
      t.is_ms_shipped,           
      t.is_disabled,             
      t.is_not_for_replication,  
      t.is_instead_of_trigger    
   FROM 
      sys.triggers t 
   WHERE 
      t.parent_id = 0
)
SELECT * FROM cte_AllTriggers ORDER BY TableName, TriggerName

执行效果如下:

在看到了上图的运行后果以后,我发现AdventureWorks数据库的触发器定义使用了一个很不错的命名约定

将Insert触发器使用以小写字母i开头以作识别

将Update触发器使用以小写字母u开头以做识别

将Delete触发器使用以小写字母d开头以做识别

注意行号为1, 2的触发器是DDL触发器所以他们的表对象为NULL.

OK,介绍完怎么查看某个数据库内所有的触发器后,接下来!我将为你介绍AdventureWorks中触发器的用途.

IV:DML触发器在AdventureWorks中的应用场景

在这里并不会把12个原有的触发器都一一讲完,仅仅挑其中2个进行讲解.
1.dEmployee:第一章第2节已经介绍
2.iuIndividual:位于Sales架构的Individual表.这是一个带插入兼更新为一体的触发器.

首先介绍一下表
Sales.Individual --客户的统计数据表
{
    CustomerID, --客户Id
    ContactID, --联系人Id
    Demographics, --统计
    ModifiedDate --上次修改日期
}

Sales.Store --客户和经销商表
{
    CustomerID, --客户Id
    Name, --商店的名称
    SalesPersonID, --Adventure Works 销售代表的Id
    Demographics, --商店的统计信息
    rowguid, --用于合并数据库
    ModifiedDate --上次修改日期
}

下面的这个触发器大概可以理解为插入或更新的表Sales.Individual的CustomerID列存在于[Sales].[Store].[CustomerID]时回滚.

当成功更新/插入列[CustomerID],[Demographics]时,根据设定好的条件更新[Sales].[Individual].[Demographics]列.

ALTER TRIGGER [Sales].[iuIndividual] 
   ON [Sales].[Individual] --该触发器是给[Sales].[Individual]定义的
   AFTER INSERT, UPDATE --是AFTER INSERT触发器的同时,也是AFTERC触发器
   NOT FOR REPLICATION
AS 
BEGIN
   DECLARE @Count int;
   SET @Count = @@ROWCOUNT;
   IF @Count = 0 
      RETURN; --如果插入删除所影响的行为0时退出
   SET NOCOUNT ON;
   
   IF EXISTS (
         SELECT * FROM 
            inserted INNER JOIN [Sales].[Store] 
               ON inserted.[CustomerID] = [Sales].[Store].[CustomerID]
      ) 
   BEGIN
      IF @@TRANCOUNT > 0
      BEGIN
         --当插入[Sales].[Individual]表客户Id已存在于[Sales].[Store]表时回滚
         ROLLBACK TRANSACTION;
      END
   END;
   
   
   IF UPDATE([CustomerID]) OR UPDATE([Demographics]) --如果CustomerID列或Demographics列更新|插入成功
   BEGIN
      --根据条件更新Demographics列
      UPDATE [Sales].[Individual] SET 
         [Sales].[Individual].[Demographics] = N'<IndividualSurvey xmlns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey">
<TotalPurchaseYTD>0.00</TotalPurchaseYTD>
</IndividualSurvey>' 
      FROM inserted --连接到插入或更新的数据表
      WHERE 
         [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
         AND inserted.[Demographics] IS NULL; --将要更新或插入的Demographics为空时

      --根据条件插入一个XML节点
      UPDATE [Sales].[Individual] SET 
         [Demographics].modify(
            N'declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
insert <TotalPurchaseYTD>0.00</TotalPurchaseYTD> 
as first 
into (/IndividualSurvey)[1]') 
      FROM inserted 
      WHERE 
         [Sales].[Individual].[CustomerID] = inserted.[CustomerID] 
         AND inserted.[Demographics] IS NOT NULL 
         AND inserted.[Demographics].exist(N'declare default element namespace 
      "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 
/IndividualSurvey/TotalPurchaseYTD') <> 1;
   END;
END;

我对AdventureWorks不太熟悉,需要学习研究的朋友们请自己上网搜索相关资料.

3.iWorkOrder:位于[Production].[WorkOrder]表的AFTER插入触发器

此触发器用于先尝试把将要插入到表[Production].[WorkOrder]的数据先插入到[Production].[TransactionHistory]表中,如果[Production].[TransactionHistory]插入完成.那么将提交事务,否则显示错误信息及回滚,并将错误写入日志表内.

ALTER TRIGGER [Production].[iWorkOrder] 
   ON [Production].[WorkOrder] 
   AFTER INSERT --AFTER插入触发器
AS 
BEGIN
   DECLARE @Count int;
   SET @Count = @@ROWCOUNT;
   IF @Count = 0 
      RETURN; --插入的行为0时退出
   
   SET NOCOUNT ON;
   BEGIN TRY
      --往工作订单表(TransactionHistory)插入生产工作订单(WorkOrder)记录
      INSERT INTO [Production].[TransactionHistory] (
         [ProductID]
         ,[ReferenceOrderID]
         ,[TransactionType]
         ,[TransactionDate]
         ,[Quantity]
         ,[ActualCost]
      )
      SELECT 
         inserted.[ProductID]
         ,inserted.[WorkOrderID]
         ,'W' --代表工作订单
         ,GETDATE()
         ,inserted.[OrderQty]
         ,0
      FROM inserted;
   END TRY
   BEGIN CATCH
      --在Message窗口中输出错误信息
      EXECUTE [dbo].[uspPrintError];
      IF @@TRANCOUNT > 0
      BEGIN
        ROLLBACK TRANSACTION; --事务数 > 0时回滚
      END
      
      --记录错误信息进日志
      EXECUTE [dbo].[uspLogError];
   END CATCH;
END;

V:触发器的弊端

学习了上面4个章节的内容后,大家都可以知道了..当你往数据库内插入,修改,删除数据时.在原来没有触发器的情况下时也仅仅需要做一下外键主键的完整性约束检查及CHECK约束检查.而在有了触发器的情况又会在原有的基础上再多跑一些T-SQL语句.所以触发器设计得是否合理对于一个数据库系统性能极为重要.在触发器内部需要注意锁的问题!
http://v.youku.com/v_show/id_XMjg5NDQ3MzUy.html (祈祷, 6++月24--)

本文到此结束!

posted @ 2011-08-01 08:47 dotNetDR_ 阅读(3341) 评论(7) 编辑收藏举报

刷新页面返回顶部

dotNetDR_ 北京一滴汗水

EMail:261621172#qq.com
专精于 Private Cloud Dev（旧 - ASP.NET）

Transact-SQL 示例 - 触发器的基础及应用

I:触发器介绍

II.如何使用触发器

III:查询一个数据库内的到底定义了多少个触发器?他们所属的表对象是谁?

IV:DML触发器在AdventureWorks中的应用场景

V:触发器的弊端

公告

dotNetDR_ 北京 一滴汗水

EMail:261621172#qq.com 专精于 Private Cloud Dev（旧 - ASP.NET）

Transact-SQL 示例 - 触发器的基础及应用

I:触发器介绍

II.如何使用触发器

III:查询一个数据库内的到底定义了多少个触发器?他们所属的表对象是谁?

IV:DML触发器在AdventureWorks中的应用场景

V:触发器的弊端

公告

dotNetDR_ 北京一滴汗水

EMail:261621172#qq.com
专精于 Private Cloud Dev（旧 - ASP.NET）