Order of tigger, constraint and user query

'...If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution.'( comes form http://msdn.microsoft.com/en-us/library/ms189799.aspx)

 

'...

AFTER

AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully . All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified.

AFTER triggers cannot be defined on views.'( comes form http://msdn.microsoft.com/en-us/library/ms189799.aspx)

'...The first and last AFTER triggers to be executed on a table can be specified by using sp_settriggerorder . Only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation can be specified on a table. If there are other AFTER triggers on the same table, they are randomly executed.' ( comes form http://msdn.microsoft.com/en-us/library/ms189799.aspx)

 

Order:

1. INSTEAD OF trigger
2. Constraints
3. User query
4. AFTER trigger (default trigger type)

Others: INSTEAD OF trigger vs. AFTER trigger vs. Constraints

http://topic.youkuaiyun.com/u/20090219/23/4727430a-0627-4c8e-bb46-3c4b2adfa421.html

INSTEAD OF 触发器用来代替通常的触发动作，即当对表进行INSERT、UPDATE 或 DELETE 操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如正确才进行相应的操作。因此，INSTEAD OF 触发器的动作要早于表的约束处理。
INSTEAD OF 触发器的操作有点类似于完整性约束。在对数据库的操纵时，有些情况下使用约束可以达到更好的效果，而如果采用触发器，则能定义比完整性约束更加复杂的约束 。有关触发器与约束的比较，请参见联机丛书。
INSTEAD OF 触发器不仅可在表上定义，还可在带有一个或多个基表的视图上定义，但在作为级联引用完整性约束目标的表上限制应用。
AFTER 触发器定义了对表执行了 INSERT、UPDATE 或 DELETE 语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用 AFTER 触发器。AFTER 触发器只能在表上指定，且动作晚于约束处理。
每一个表上只能创建一个 INSTEAD OF 触发器，但可以创建多个 AFTER 触发器。
实例:
INSTEAD OF 触发器:
向表“计0261”插入数据时，检查学号是否存在于表“计026”中，如存在则进行插入操作，否则就不插入。

SQL code

   
   

    
    CREATE

    
     

    
    TRIGGER

    
     

    
    [

    
    checkid

    
    ]

    
     

    
    ON

    
     

    
    [

    
    dbo

    
    ]

    
    .

    
    [

    
    计0261

    
    ]

    
     

INSTEAD 

    
    OF

    
     

    
    insert

    
    



    
    AS

    
    



    
    IF

    
     

    
    NOT

    
     

    
    EXISTS

    
    (

    
    SELECT

    
     

    
    *

    
     

    
    FROM

    
     计026 

    
    WHERE

    
     学号

    
    =

    
    (

    
    SELECT

    
     学号 

    
    FROM

    
     INSERTED))

  

    
    BEGIN

    
    

    

    
    ROLLBACK

    
     

    
    TRANSACTION

    
    

    

    
    PRINT

    
     

    
    '

    
    要处理记录的学号不存在！

    
    '

    
    

  

    
    END

    
    



    
    ELSE

    
    

  

    
    BEGIN

    
    

    

    
    INSERT

    
     

    
    INTO

    
     计0261 

    
    select

    
     

    
    *

    
     

    
    from

    
     inserted

    

    
    PRINT

    
     

    
    '

    
    已经成功处理记录！

    
    '

    
    

  

    
    END

   
   

 
 

  
  AFTER 触发器:
  
  

对订货表设置 AFTER（FOR） 类型的 INSERT 触发器，用来在插入记录时自动将统计值计算到订货统计表中。
 
 

SQL code

   
   

    
    CREATE

    
     

    
    TRIGGER

    
     

    
    [

    
    orderinsert

    
    ]

    
     

    
    ON

    
     

    
    [

    
    dbo

    
    ]

    
    .

    
    [

    
    订货表

    
    ]

    
    

AFTER 

    
    INSERT

    
    



    
    AS

    
    



    
    DECLARE

    
     

    
    @bookid

    
     

    
    int

    
    , 

    
    @ordernum

    
     

    
    int

    
    , 

    
    @num

    
     

    
    int

    
    



    
    SELECT

    
     

    
    @bookid

    
     

    
    =

    
     书籍编号, 

    
    @ordernum

    
     

    
    =

    
     数量 

    
    FROM

    
     INSERTED



    
    SELECT

    
     

    
    @num

    
     

    
    =

    
     

    
    count

    
    (书籍编号) 

    
    FROM

    
     订货统计表 

    
    WHERE

    
     书籍编号

    
    =

    
    @bookid

    
    



    
    IF

    
     

    
    @num

    
     

    
    =

    
     

    
    0

    
    

  

    
    --

    
    未找到该书，插入记录

    
    



    
      

    
    INSERT

    
     

    
    INTO

    
     订货统计表 

    
    VALUES

    
    (

    
    @bookid

    
    , 

    
    @ordernum

    
    )



    
    ELSE

    
    

  

    
    --

    
    找到该书，更新记录

    
    



    
      

    
    UPDATE

    
     订货统计表



    
    SET

    
     总订货量 

    
    =

    
     总订货量 

    
    +

    
     

    
    @ordernum

    
     

    
    WHERE

    
     书籍编号 

    
    =

    
     

    
    @bookid