约束类触发器

触发器综述之一

约束类触发器

   

简单的说，约束类触发器的功能是检查进行的对表的操作是否合乎规则，阻止非法操作并提示问题。

我们知道，SQL SERVER有主键约束、唯一性约束、外键约束、CHECK约束、DEFAULT约束、NOT NULL约束和触发器来维护数据库的完整性和一致性，主键约束、唯一性约束是定义键的关系的，用来维护实体完整性，DEFAULT约束只用来定义默认值，NOT NULL 约束定义一个列是否必须，CHECK约束一般用来定义一个表内一个或者多个字段之间的互相约束，是用来维护域完整性的，只有外键约束和触发器可以用来定义表之间的约束，用来维护引用完整性。

触发器定义约束是最灵活的方法，它可以用来代替所有的约束，但是一般我们不提倡用触发器来约束，主要原因是触发器相对于主键约束、唯一性约束、外键约束、CHECK约束和DEFAULT约束来说，效率都比较低，可能会引发性能问题。但是，我们推荐一下情况下使用约束类触发器：

1、 除某个特殊值外唯一的约束需求，比如一个字段，除了“其他”之外，必可以重复。

2、 很难用CHECK和RULE描述的单个或者多个字段之间的约束。

3、 类似于外键约束，但是主表字段组合没有定义主键和唯一索引的情况。

4、 其他的不好用其他约束描述的业务逻辑。

5、 保护数据，比如不允许删除和修改。

 

从功能上来说，一般此类触发器往往会判断是否违反规则，如果违反规则，抛出错误、回滚事务并返回。所以这类触发器的主要代码在于判断是否违反规则，而规则是根据用户业务定义的。抛出错误、回滚事务并返回的代码可以非常统一，而抛出错误的错误信息可以在触发器中用文本的方式直接抛出，也可以定义到数据库的消息库。

从技术上来说，这类触发器的复杂程度主要看需要判断的规则的复杂程度，简单的可能几句话就实现，比如如下触发器，判断记录的日期字段，如果小于2008-1-1就不允许操作。

 

--测试表  

  CREATE   TABLE   TBTEST(  

  ID   INT,  

  INFO   VARCHAR(10),

  DATE  DATETIME

  )  

  GO  

   

  --触发器  

  CREATE   TRIGGER   TR_TBTEST   ON   TBTEST  

  FOR   INSERT,DELETE,UPDATE  

  AS  

   

 

  SET   NOCOUNT   ON  

 

  IF    EXISTS ( SELECT 1 FROM INSERTED WHERE DATE<'2008-1-1') OR

    EXISTS ( SELECT 1 FROM DELETED WHERE DATE<'2008-1-1')

  BEGIN

           RAISERROR ('不能操作2008-01-01前的记录.', 16, 1)
      
      

          ROLLBACK TRANSACTION

  END

  GO  

 

--测试

 

--先在企业管理器输入两条记录  
  --ID     INFO   DATE  
  --1     1      2008-2-1
  --2     2      2007-12-31
   

--在添加第一条记录正常，添加第二条记录出现如下信息：

      

 

--再执行语句：  
  INSERT   TBTEST  
  VALUES(3,'3','2007-2-1')  

  GO  
    结果

 

 

UPDATE   TBTEST  
  SET   DATE='2007-3-1'  
  WHERE   ID=1  
  GO

结果如上
  
  SELECT   *   FROM   TBTEST  
  GO  
   
 -结果

 

 

这是个比较简单的例子，我再写个复杂点的例子，TB1和TB2是两个主表，TB3是从表，其TYPE字段决定引用哪个表，这里三个表都加了触发器，用来维护三表之间的引用完整性，提示信息业根据记录情况变化：

 

--测试表  

CREATE TABLE TB1(

ID INT,

INF1    VARCHAR(10)

)

GO

 

CREATE TABLE TB2(

ID INT,

INF2    VARCHAR(10)

)

GO

 

CREATE TABLE TB3(

ID INT,

INF3    VARCHAR(10),

TYPE    INT,

RID     INT

)

GO

 

--触发器

CREATE TRIGGER TR_TB1 ON TB1

FOR DELETE,UPDATE           --只需要检查删改操作

AS

SET NOCOUNT ON

DECLARE     @MSG    VARCHAR(200)

 

IF NOT EXISTS (SELECT 1 FROM INSERTED)  --删除操作

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF1+')被TB3记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+')引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      --删除的纪录被TB3引用

    AND A.TYPE=1        --引用TB1

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG='不能删除TB1记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

 

IF EXISTS (SELECT 1 FROM INSERTED)  --修改操作

    AND UPDATE(ID)          --修改了ID

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF1+')的ID被TB3记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+')引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      --删除的纪录被TB3引用

    AND A.TYPE=1        --引用TB1

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG='不能修改TB1记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

   

GO

 

--触发器

CREATE TRIGGER TR_TB2 ON TB2

FOR DELETE,UPDATE           --只需要检查删改操作

AS

SET NOCOUNT ON

DECLARE     @MSG    VARCHAR(200)

 

IF NOT EXISTS (SELECT 1 FROM INSERTED)  --删除操作

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF2+')被TB3记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+')引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      --删除的纪录被TB3引用

    AND A.TYPE=2        --引用TB2

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG='不能删除TB2记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

 

IF EXISTS (SELECT 1 FROM INSERTED)  --修改操作

    AND UPDATE(ID)          --修改了ID

BEGIN

    SELECT  @MSG=ISNULL(@MSG+',','')+CAST(D.ID AS VARCHAR)+'('+D.INF2+')的ID被TB3记录'+CAST(A.ID AS VARCHAR)+'('+A.INF3+')引用'

    FROM    TB3 A,DELETED D

    WHERE   A.RID=D.ID      --删除的纪录被TB3引用

    AND A.TYPE=2        --引用TB2

   

    IF @MSG IS NOT NULL

    BEGIN

        SET @MSG='不能修改TB2记录'+@MSG

        RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

        RETURN

    END

END

   

GO

 

 

--触发器

CREATE TRIGGER TR_TB3 ON TB3

FOR INSERT,UPDATE           --需要检查增改操作，不需要检查删除

AS

SET NOCOUNT ON

DECLARE     @MSG    VARCHAR(200)

 

--不必分操作类型检测

SELECT  @MSG=ISNULL(@MSG+',','')+CAST(I.ID AS VARCHAR)+'('+I.INF3+')'

FROM    INSERTED I

WHERE   NOT EXISTS (

    SELECT  1

    FROM    TB1 T

    WHERE   I.RID=T.ID     

    AND I.TYPE=1        --引用TB1

    )

AND     NOT EXISTS (

    SELECT  1

    FROM    TB2 T

    WHERE   I.RID=T.ID     

    AND I.TYPE=2        --引用TB2

    )

 

IF @MSG IS NOT NULL

BEGIN

    SET @MSG='不能增加或者修改TB3记录'+@MSG+',没有主表记录'

    RAISERROR (@MSG, 16, 1)

    ROLLBACK TRANSACTION

    RETURN

END

   

GO

 

 

--测试

--准备数据

INSERT TB1 

SELECT  1,'AAA'

UNION ALL SELECT 2,'BBB'

GO

 

INSERT TB2 

SELECT  1,'CCC'

UNION ALL SELECT 3,'DDD'

GO

 

INSERT TB3 

SELECT  1,'EEE',1,1

UNION ALL SELECT 2,'FFF',1,2

UNION ALL SELECT 3,'GGG',2,1

UNION ALL SELECT 4,'HHH',2,3

 

GO

 

--结果 到现在没有问题

--TB1删改(TB2操作基本相同)

 

DELETE TB1

WHERE ID=1

GO

 

结果

 

 

UPDATE TB1

SET ID=5

WHERE ID=2

GO

 

--结果

 

 

DELETE TB1

GO

 

 

--TB3增改

 

INSERT TB3

VALUES(5,'III',1,3)

GO

 

--结果

 

 

UPDATE TB3

SET INF3='HAI'

WHERE ID=2

GO

 

--结果

正常

 

UPDATE TB3

SET TYPE=3

WHERE ID=2

 

GO

--结果

 

 

UPDATE TB3

SET RID=3

WHERE ID=2

 

GO

--结果

 

 

总结：约束类触发器是数据库完整性维护的重要工具，在不能用其他更好的数据库约束进行定义的情况下，如果不用触发器，这类约束功能就只能交给客户端程序来实现，所以其各种性能的比较我们应该主要与客户端程序来比较：

1、 用触发器实现程序代码比较集中，容易维护和迁移。

2、 速度上应该是触发器较快。

3、 由于触发器代码编写困难的问题，对于需要逐条记录循环判断的规则，触发器可能需要用游标来处理，对于这样的规则，可能用触发器编写会有些困难，但是如果能很好的理解触发器的工作原理，大部分游标都是可以用一般SQL语句来实现的。

约束类触发器编写的主要难点在于关于需要根据操作类型判断是否符合规则，需要编写者有很好的业务逻辑理解和集合操作的思想，这里的所谓集合操作的思想是我自己给的名词，意思是我们在写后台程序（包括触发器、存储过程、自定义函数）的时候，不能用前台程序的逐条记录处理的思路来考虑问题，而是尽量用操作集合的思想来实现我们的业务逻辑。