SQL Server 触发器学习总结

SQL菜鸟入门级教程之触发器

  
触发器简介：
 
触发器（trigger）是种特殊的存储过程，它的执行不是由程序调用，也不需要手动操作，它是由事件来触发，事件大家应该非常熟悉吧，比如按钮的Click事件、网页的Load事件等。按钮的Click事件是通过鼠标单击按钮触发的，而触发器的事件，是由对表进行增删改操

作所触发的。当对一个数据库或表进行增删改（ Insert，Delete，Update）的时就会激活触发器。 
 
从SQL2005开始，根据SQL语句的不同将触发器分成了两类，一类是DML触发器，一类是DLL触发器。其中DML触发器又分为两类：After触发器和Instead Of触发器。
 
触发器的分类：
 
DML触发器：DML（Data Manipulation Language）触发器是当数据库服务器中发生数据操作语言事件时执行的存储过程。DML触发器又分为两类：After触发器和Instead Of触发器
DDL触发器：DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。
 
DML触发器：
 
今天我们我们主要介绍DML触发器，DML触发器分为After触发器和Instead Of触发器。
After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。
Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。
 
在SQL Server里，每个DML触发器都分配有两个特殊的表，一个是Inserted表，一个是Deleted表。它们两个存在于数据库服务器的内存中，是由系统管理的逻辑表，是两个临时表，而不是真正存储在数据库中的物理表。用户对这两个表只有读取的权限，没有修改的权限。
这两个表的结构(主外键、字段、数据类型等)与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。
 
Inserted和Deleted两个表的作用：
 
Inserted：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。
Deleted：对于更新记录操作来说，删除表里存放的是被更新记录；对于删除记录操作来说，删除表里存入的是被删除的旧记录。
看上面两句话可能不太明白，那么看看下面这张表是不是就明白了？
  
工作原理：
 
After触发器的工作原理：
 
After触发器是在SQL语句执行之后才被激活的。以删除记录为例：当SQL Server接收到一条删除操作的SQL语句时，SQL Server先将要删除的记录存放在Deleted表里，然后把数据表里的记录删除，再激活After触发器，执行After触发器里的SQL语句。执行完毕之后， 删

除内存中的Deleted表，操作结束。
还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除的时候，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。数据库的操作如下：
 
1，接收SQL语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。
2，从产品库存表里删除该产品记录。
3，从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从内存里清除删除表；如果不为零的话，用Rollback Transaction语句来回滚操作(即将库存表还原成删除之前的状态)。
 
Instead Of 触发器的工作原理：
 
Instead Of触发器与After触发器不同。After触发器是在Insert、Update和Delete操作完成后才激活的，而Instead Of触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而是用触发器本身的SQL语句代替原来的语句去执行。
还拿上面那个例子来说，删除一条产品记录的时候，用Instead Of将删除操作替换成查询该产品的库存。数据库的操作如下：
 
1，接收SQL语句，但不执行，而是跳转到Instead Of后面的SQL语句
2，根据传入的产品ID，将该产品的库存取出，完成操作。
 
下面让我们看看触发器的代码到底怎么写：
 
下面的一段代码是触发器的一个框架。
 
CREATE TRIGGER  Trigger_Name --触发器名，在一个数据库中触发器名是唯一的。
 
   ON  Table_Name | View_Name --触发器所在的表或者视图。
 
   AFTER(FOR)|Instead Of  INSERT,DELETE,UPDATE --定义成AFTER或Instead Of类型的触发器。
   
   --AFTER跟FOR相同，不可在视图上定义AFTER触发器
   
   -- 后面是触发器被触发的条件，最少有一个，可以邮多个。如果有多个用逗号分开，顺序无要求。  www.2cto.com  
 
AS --触发器要执行的操作
 
BEGIN
 
   --BEGIN跟END组成一个代码块，可以写也可以不写，如果触发器中执行的SQL语句比较复杂，用BEGIN和END会让代码更加整齐，更容易理解。
 
END
GO --GO就代表结操作完毕
 
注意事项：  www.2cto.com  
 
1，After触发器只能用于数据表不能用于视图；Instead Of触发器两者皆可，设置为With Check Option的视图也不允许建立Instead Of触发器。两种触发器都不可以建立在临时表上。
2，一个数据表可以有多个触发器，但是一个触发器只能对应一个表。
3，在同一个数据表中，对每个操作（如Insert、Update、Delete）而言可以建立许多个After触发器，而Instead Of触发器针对每个操作只有建立一个。
4，如果针对某个操作即设置了After触发器又设置了Instead Of触发器，那么Instead of触发器一定会激活，而After触发器就不一定会激活。
 
5，不同的SQL语句，可以触发同一个触发器，如Insert和Update语句都可以激活同一个触发器。
6，触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的，所以有很多人都以为只要是在不同的数据表中，触发器的名称就可以相同，其实触发器的全名（Server.Database.Owner.TriggerName）是必须 唯一的，这与触发器在哪个数据表或视图无关。
7，关键字AFTER可以用For来代取，它们的意思都是一样的，代表只有在数据表的操作都已正确完成后才会激活的触发器。

========

简单触发器的使用 献给SQL初学者

简单触发器的使用 献给SQL初学者，使用sqlserver的朋友可以参考下。

 首先，啰嗦几句废话如下： 
　　（1）触发器（trigger）是个特殊的存储过程，它的执行并不需要我们去显式调用，而是由一些事件触发，这有点类似C#中的事件处理机制。当使用UPDATE，INSERT　　或DELETE的一种或多种对指定的数据库的相关表进行操作时，会触发触发器。 
　　（2）触发器可以包含复杂的SQL语句，主要用于强制复杂的业务规则或要求。 
　　（3）触发器能够维持数据库的完整性，当执行插入、更新或删除操作时，触发器会根据表与表之间的关系，强制保持其数据的完整性。 
　　 
　　好，啰嗦完了开始贴代码，首先贴上我创建的两张表所包含的列，他们的关联关系是1对多，以UserID进行关联。 


然后来一个非常简单的触发器 
 

IF EXISTS(SELECT * FROM sysobjects 
WHERE name='tr_Users_OnUpdate' AND TYPE='TR') 
DROP TRIGGER tr_Users_OnUpdate 
GO --这里呢创建触发器与存储过程类似（都是DDL） 
--先判断如否存在同名触发器就删除然后重建 
CREATE TRIGGER tr_Users_OnUpdate 
ON Users FOR UPDATE 
AS PRINT ‘Users表已发生修改' 
GO

 
上述代码中，tr_Users_OnUpdate为触发器名称，Users为表名。这触发器的作用是当向Users表执行Update时将打印“Users表已发生修改”。 
　　好了我们可以看到这个触发器的实用性不是很大，那么接下来呢我们再来学习下关于触发器里两种特殊的表“inserted”和“deleted”。这两张表主要用于触发器。Deleted 表用于存储 执行DELETE 和 UPDATE操作时所影响的行的副本。而Inserted 表则用于存储 

INSERT 和 UPDATE 语句所影响的行的副本。那么我们看到执行UPDATE操作时都会有记录分别存储到“inserted”和“deleted”。其实理解起来不难deleted表存储的是Update之前的记录，而inserted存储的呢则是Update之后的记录，这里关于理论性东西我不再赘述，官

方资料有更详细说明。 
　　现在我们要做的就是本文的重点，当往WordInfo添加一条记录时，使用触发器使UserInfo的相应记录的LeaveCount字段增加1。代码如下： 
 

--添加留言的触发器 
IF EXISTS(SELECT name FROM sysobjects WHERE name='tr_LeaveWord_Add' AND TYPE='TR') 
DROP TRIGGER tr_LeaveWord_Add 
GO 
CREATE TRIGGER tr_LeaveWord_Add 
ON WordInfo FOR INSERT 
AS UPDATE UserInfo SET LeaveCount=LeaveCount+1 
WHERE UserID=(SELECT TOP 1 UserID FROM Inserted) 
GO

OK,到这里就可以收工了，值得注意的是如果触发器是UPDATE触发的，那么在执行Update后再查询更新之前的数据改成查询deleted表即可。 

========

初学sql server 2008之触发器

　　触发器(trigger)：是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，从而保证对数据的不完整性约束或不正确的修改。


　　在SQL SERVER 2008中，有三种类型的触发器：


(1)DML触发器：是指触发器在数据库中发生数据操作语言(DML)事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句也。


(2)DDL触发器：是指当服务器或数据库中发生数据定义语言(DDL)事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。


(3)登陆触发器：是指当用户登录SQL SERVER实例建立会话时触发。


　　不过根据DML触发器触发的方式不同又分为以下两种情况：


（1）AFTER触发器：它是在执行INSERT、UPDATE、DELETE语句操作之后执行触发器操作。它主要是用于记录变更后的处理或检查，一旦发生错误，可以用Rollback Transaction语句来回滚本次扣件，不过不能对视图定义AFTER触发器。


（2）INSTEAD OF触发器：它在执行INSERT、UPDATE、DELETE语句操作之前执行触发器本身所定义的操作。而INSTEAD OF触发器是可以定义在视图上的。


 　　在SQL SERVER 2008中，DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中，由系统管理的逻辑表，我们对于它只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后


，它们也就会被自动删除。


INSERED表用于存放你在操件insert、update、delete语句后，更新的记录。比如你插入一条数据，那么就会把这条记录插入到INSERTED表。


DELETED表用于存放你在操作 insert、update、delete语句前，你创建触发器表中数据库。比如你原来的表中有三条数据，那么他也有三条数据。


下面我们就开始创建触发器吧!


我们先创建一张如下的表吧！然后以它为例吧。


下面就是创建一个简单的after触发器：


create trigger trigMessageList 
on table1 
after insert,update 
as raiserror('数据一致性验证',16,1)
此时如果我们手动打开table1，输入一条，根本就插入不了，我们只能写一条如下的SQL语句插入一条数据


insert into table1(user_name)values('23')
结果数据是插入成功了，但是会出显如下图所示的提示：






下面我再创建一个稍微有点复杂的after触发器：


--该触发器作用是：如果向table1中插入数据时，
--先检查要向table1插入的这条的USER_ID是否USERS(我创建的另一个表)表中的USER_ID字段中有，如果不存在则不允许插入
create trigger trigInsertedMessages 
on table1 
after insert 
as 
if exists (select * from inserted a where a.user_id not in (select USER_ID from USERS)) 
begin 
raiserror('数据一致性验证',16,1) 
rollback transaction 
end
 


inserted of触发器和after触发器写法差不多，在此就不多写了。


下面创建一个简单的DDL触发器：


复制代码
--它的作用是：防止数据库Test(我创建的一个实验数据库)中的任一表被修改或删除
create trigger trigDB 
on database 
for drop_table,alter_table 
as 
print '你一定要禁用触发器“trigDB"才能删除或修改这个数据库的表' 
rollback


删除触发器：


drop trigger trigDB 
ON DATABASE
一定要记得加上ON DATABASE这条，否则就删除不了。
最后再来条开启和禁用触发器的语句：


disable trigger trigDB on database --禁用触发器
enable trigger trigDB on database --开启触发器


========

SQL Server 触发器

触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。 


什么是触发器 


    触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作。诸如：update、insert、delete这些操作的时候，系统会自动调用执行该表


上对应的触发器。SQL Server 2005中触发器可以分为两类：DML触发器和DDL触发器，其中DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。 


  


    DML触发器分为： 


    1、 after触发器（之后触发） 


        a、 insert触发器 


        b、 update触发器 


        c、 delete触发器   


    2、 instead of 触发器 （之前触发）   


    其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。 


    触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。


对表的操作
 Inserted逻辑表
 Deleted逻辑表
 
增加记录（insert）
 存放增加的记录
 无
 
删除记录（delete）
 无
 存放被删除的记录
 
修改记录（update）
 存放更新后的记录
 存放更新前的记录
 

    Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是：触发器本身就是一个事务，所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚，撤销操作。 


Ø 创建触发器 

    语法 

create trigger tgr_nameon table_namewith encrypion –加密触发器    for update...as    Transact-SQL

    # 创建insert类型触发器 

--创建insert插入类型触发器if (object_id('tgr_classes_insert', 'tr') is not null)    drop trigger tgr_classes_insertgocreate trigger tgr_classes_inserton classes    for insert --插入触发as    --定义变量    declare @id int, @name varchar


(20), @temp int;    --在inserted表中查询已经插入记录信息    select @id = id, @name = name from inserted;    set @name = @name + convert(varchar, @id);    set @temp = @id / 2;        insert into student values(@name, 18 + @id, @temp, 


@id);    print '添加学生成功！';go--插入数据insert into classes values('5班', getDate());--查询数据select * from classes;select * from student order by id;     insert触发器，会在inserted表中添加一条刚插入的记录。 
 


    # 创建delete类型触发器 


--delete删除类型触发器if (object_id('tgr_classes_delete', 'TR') is not null)    drop trigger tgr_classes_deletegocreate trigger tgr_classes_deleteon classes    for delete --删除触发as    print '备份数据中……';        if (object_id


('classesBackup', 'U') is not null)        --存在classesBackup，直接插入数据        insert into classesBackup select name, createDate from deleted;    else        --不存在classesBackup创建再插入        select * into classesBackup from 


deleted;    print '备份数据成功！';go----不显示影响行数--set nocount on;delete classes where name = '5班';--查询数据select * from classes;select * from classesBackup;   delete触发器会在删除数据的时候，将刚才删除的数据保存在deleted表中。 



    # 创建update类型触发器 


--update更新类型触发器if (object_id('tgr_classes_update', 'TR') is not null)    drop trigger tgr_classes_updategocreate trigger tgr_classes_updateon classes    for updateas    declare @oldName varchar(20), @newName varchar(20);    --更新


前的数据    select @oldName = name from deleted;    if (exists (select * from student where name like '%'+ @oldName + '%'))        begin            --更新后的数据            select @newName = name from inserted;            update student 


set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';            print '级联修改数据成功！';        end    else        print '无需修改student表！';go--查询数据select * from student order by id;select * from 


classes;update classes set name = '五班' where name = '5班';     update触发器会在更新数据后，将更新前的数据保存在deleted表中，更新后的数据保存在inserted表中。 


    # update更新列级触发器 


if (object_id('tgr_classes_update_column', 'TR') is not null)    drop trigger tgr_classes_update_columngocreate trigger tgr_classes_update_columnon classes    for updateas    --列级触发器：是否更新了班级创建时间    if (update


(createDate))    begin        raisError('系统提示：班级创建时间不能修改！', 16, 11);        rollback tran;    endgo--测试select * from student order by id;select * from classes;update classes set createDate = getDate() where id = 


3;update classes set name = '四班' where id = 7;     更新列级触发器可以用update是否判断更新列记录； 
 


    # instead of类型触发器 


       instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身的内容。 

       创建语法 

create trigger tgr_nameon table_namewith encryption    instead of update...as    T-SQL    


      # 创建instead of触发器


if (object_id('tgr_classes_inteadOf', 'TR') is not null)    drop trigger tgr_classes_inteadOfgocreate trigger tgr_classes_inteadOfon classes    instead of delete/*, update, insert*/as    declare @id int, @name varchar(20);    --查询被删


除的信息，病赋值    select @id = id, @name = name from deleted;    print 'id: ' + convert(varchar, @id) + ', name: ' + @name;    --先删除student的信息    delete student where cid = @id;    --再删除classes的信息    delete classes where id 


= @id;    print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';go--testselect * from student order by id;select * from classes;delete classes where id = 7;    


      # 显示自定义消息raiserror


if (object_id('tgr_message', 'TR') is not null)    drop trigger tgr_messagegocreate trigger tgr_messageon student    after insert, updateas raisError('tgr_message触发器被触发', 16, 10);go--testinsert into student values('lily', 22, 1, 


7);update student set sex = 0 where name = 'lucy';select * from student order by id;


    # 修改触发器


alter trigger tgr_messageon studentafter deleteas raisError('tgr_message触发器被触发', 16, 10);go--testdelete from student where name = 'lucy';


    # 启用、禁用触发器 


--禁用触发器disable trigger tgr_message on student;--启用触发器enable trigger tgr_message on student;


    # 查询创建的触发器信息 


--查询已存在的触发器select * from sys.triggers;select * from sys.objects where type = 'TR';--查看触发器触发事件select te.* from sys.trigger_events te join sys.triggers ton t.object_id = te.object_idwhere t.parent_class = 0 and t.name = 


'tgr_valid_data';--查看创建触发器语句exec sp_helptext 'tgr_message';


    # 示例，验证插入数据 


if ((object_id('tgr_valid_data', 'TR') is not null))    drop trigger tgr_valid_datagocreate trigger tgr_valid_dataon studentafter insertas    declare @age int,            @name varchar(20);    select @name = s.name, @age = s.age from 


inserted s;    if (@age < 18)    begin        raisError('插入新数据的age有问题', 16, 1);        rollback tran;    endgo--testinsert into student values('forest', 2, 0, 7);insert into student values('forest', 22, 0, 7);select * from 


student order by id;


    # 示例，操作日志 


if (object_id('log', 'U') is not null)    drop table loggocreate table log(    id int identity(1, 1) primary key,    action varchar(20),    createDate datetime default getDate())goif (exists (select * from sys.objects where name = 


'tgr_student_log'))    drop trigger tgr_student_loggocreate trigger tgr_student_logon studentafter insert, update, deleteas    if ((exists (select 1 from inserted)) and (exists (select 1 from deleted)))    begin        insert into log


(action) values('updated');    end    else if (exists (select 1 from inserted) and not exists (select 1 from deleted))    begin        insert into log(action) values('inserted');    end    else if (not exists (select 1 from inserted) and 


exists (select 1 from deleted))    begin        insert into log(action) values('deleted');    endgo--testinsert into student values('king', 22, 1, 7);update student set sex = 0 where name = 'king';delete student where name = 


'king';select * from log;select * from student order by id;

========

 SQL Server触发器创建、删除、修改、查看

　　一：触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束。


　　二：SQL Server为每个触发器都创建了两个专用表：Inserted表和Deleted表。这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后，与该触发器相关的这两个表也被删除。


　　Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。


　　Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。


　　三：Instead of 和 After触发器


　　SQL Server2000提供了两种触发器：Instead of 和After 触发器。这两种触发器的差别在于他们被激活的同：


　　Instead of触发器用于替代引起触发器执行的T-SQL语句。除表之外，Instead of 触发器也可以用于视图，用来扩展视图可以支持的更新操作。


　　After触发器在一个Insert,Update或Deleted语句之后执行，进行约束检查等动作都在After触发器被激活之前发生。After触发器只能用于表。


　　一个表或视图的每一个修改动作(insert,update和delete)都可以有一个instead of 触发器，一个表的每个修改动作都可以有多个After触发器。


　　四：触发器的执行过程


　　如果一个Insert﹑update或者delete语句违反了约束，那幺After触发器不会执行，因为对约束的检查是在After触发器被激动之前发生的。所以After触发器不能超越约束。


　　Instead of 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为Instead of 触发器在约束之前执行，所以它可以对约束进行一些预处理。


　　五：使用T-SQL语句来创建触发器


　　基本语句如下：


　　create trigger trigger_name


　　on {table_name | view_name}


　　{for | After | Instead of }


　　[ insert, update,delete ]


　　as


　　sql_statement


　　六：删除触发器：


　　基本语句如下：


　　drop trigger trigger_name


　　七：查看数据库中已有触发器：


　　-- 查看数据库已有触发器


　　use jxcSoftware


　　go


　　select * from sysobjects where xtype='TR'


　　-- 查看单个触发器


　　exec sp_helptext '触发器名'


　　八：修改触发器：


　　基本语句如下：


　　alter trigger trigger_name


　　on {table_name | view_name}


　　{for | After | Instead of }


　　[ insert, update,delete ]


　　as


　　sql_statement


　　九：相关示例：


　　1：在Orders表中建立触发器，当向Orders表中插入一条订单记录时，检查goods表的货品状态status是否为1(正在整理)，是，则不能往Orders表加入该订单。


　　create trigger orderinsert


　　on orders


　　after insert


　　as


　　if (select status from goods,inserted


　　where goods.name=inserted.goodsname)=1


　　begin


　　print 'the goods is being processed'


　　print 'the order cannot be committed'


　　rollback transaction --回滚，避免加入


　　end


　　2：在Orders表建立一个插入触发器，在添加一条订单时，减少Goods表相应的货品记录中的库存。


　　create trigger orderinsert1


　　on orders


　　after insert


　　as


　　update goods set storage=storage-inserted.quantity


　　from goods,inserted


　　where


　　goods.name=inserted.goodsname


　　3：在Goods表建立删除触发器，实现Goods表和Orders表的级联删除。


　　create trigger goodsdelete


　　on goods


　　after delete


　　as


　　delete from orders


　　where goodsname in


　　(select name from deleted)


　　4：在Orders表建立一个更新触发器，监视Orders表的订单日期(OrderDate)列，使其不能手工修改.


　　create trigger orderdateupdate


　　on orders


　　after update


　　as


　　if update(orderdate)


　　begin


　　raiserror(' orderdate cannot be modified',10,1)


　　rollback transaction


　　end


　　5：在Orders表建立一个插入触发器，保证向Orders表插入的货品名必须要在Goods表中一定存在。


　　create trigger orderinsert3


　　on orders


　　after insert


　　as


　　if (select count(*) from goods,inserted where goods.name=inserted.goodsname)=0


　　begin


　　print ' no entry in goods for this order'


　　rollback transaction


　　end


　　6：Orders表建立一个插入触发器，保证向Orders表插入的货品信息要在Order表中添加


　　alter trigger addOrder


　　on Orders


　　for insert


　　as


　　insert into Order


　　select inserted.Id, inserted.goodName,inserted.Number from inserted


========

SQL Server：触发器详解

1. 概述
2. 触发器的分类
3. Inserted和Deleted表
4. 触发器的执行过程
5. 创建触发器
6. 修改触发器：
7. 删除触发器：
8. 查看数据库中已有触发器：
9. “Instead of”相关示例:
10. “After”触发器
11. 参考资源
1. 概述
触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。 所以触发器可以用来实现对表实施复杂的完整性约束。


2. 触发器的分类
SQL Server2000提供了两种触发器:“Instead of” 和“After” 触发器。


一个表或视图的每一个修改动作(Insert、Update和Delete)都可以有一个“Instead of” 触发器，一个表的每个修改动作都可以有多个“After”触发器。


2.1 “Instead of”触发器


“Instead of”触发器在执行真正“插入”之前被执行。除表之外，“Instead of” 触发器也可以用于视图，用来扩展视图可以支持的更新操作。
“Instead of”触发器会替代所要执行的SQL语句，言下之意就是所要执行SQL并不会“真正执行”


alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    select 学号, 姓名 from deleted
end
 
delete from 学生 where 学号 = 4
上例中定义了“trigger学生_Delete”触发器，该触发器从“delete”表中打印出所要删除的学生.在执行“delete”操作后，会发现“学号 = 4”的学生并未被删除， 原因在于“trigger学生Delete”替代了所要执行的“delete from 学生 where 学号 = 4”语句，而


在“trigger学生_Delete”中并未真正删除学生。


2.2 “After”触发器


“After”触发器在Insert、Update或Deleted语句执行之后被触发。“After”触发器只能用于表。
“After”触发器主要用于表在修改后（insert、update或delete操作之后），来修改其他表
3. Inserted和Deleted表
SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。


这两个表由系统来维护，它们存在于内存中而不是在数据库中，可以理解为一个虚拟的表。
这两个表的结构总是与被该触发器作用的表的结构相同。
触发器执行完成后，与该触发器相关的这两个表也被删除。
Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。
对表的操作 Inserted逻辑表 Deleted逻辑表
增加记录（insert） 存放增加的记录 无
删除记录（delete） 无 存放被删除的记录
修改记录（update） 存放更新后的记录 存放更新前的记录
4. 触发器的执行过程
如果一个Insert﹑update或者delete语句违反了约束，那么这条SQL语句就没有执行成功，因此“After”触发器也不会被激活。


“Instead of” 触发器可以取代激发它的操作来执行。它在Inserted表和Deleted表刚刚建立，其它任何操作还没有发生时被执行。因为“Instead of” 触发器在约束之前执行，所以它可以对约束进行一些预处理。


5. 创建触发器


create trigger trigger_name 
on  {table_name|view_name} 
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句
6. 修改触发器：


alter trigger trigger_name 
on  {table_name|view_name} 
{After|Instead of} {insert|update|delete}
as 相应T-SQL语句
7. 删除触发器：


drop trigger trigger_name
8. 查看数据库中已有触发器：
8.1 查看数据库中所有触发器




select * from sysobjects where xtype='TR'
8.2 查看单个触发器


exec sp_helptext '触发器名'
9. “Instead of”相关示例:
两张表：学生(学号 int, 姓名 varchar)、借书记录(学号 int, 图书编号 int)


实现功能：在删除学生表时，如果该学生仍有借书记录（未还）则不能删除


alter trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号)
        delete from 学生 where 学生.学号 in (select 学号 from deleted)
end
10. “After”触发器
10.1 在“订单”表中建立触发器，当向“订单”表中插入一条订单记录时，检查“商品”表的货品状态“状态”是否为1(正在整理)，则不能往“订单”表加入该订单。




create trigger trigger_订单_insert
on 订单
after insert
as
    if (select 状态 from 商品, inserted where 商品.pid = inserted.pid)=1 
    begin
        print 'the goods is being processed'
        print 'the order cannot be committed'
        rollback transaction --回滚，避免加入 
    end
该示例中“pid”为商品编码
该示例的if判断严格来讲是不准确的，因为“订单”表如果每次插入一条记录，该判断没有问题；如果一次插入多条记录，则“select 状态”返回的是多行。
10.2 在“订单”表建立一个插入触发器，在添加一条订单时，减少“商品”表相应的货品记录中的库存。


create trigger trigger_订单_insert2
on 订单
after insert
as
    update 商品 set 数量 = 数量 - inserted.数量
    from 商品, inserted 
    where 商品.pid = inserted.pid
10.3 在“商品”表建立删除触发器，实现“商品”表和“订单”表的级联删除。


create trigger goodsdelete trigger_商品_delete
on 商品
after delete
as
    delete from 订单 where 订单.pid in (select pid from deleted)
10.4 在“订单”表建立一个更新触发器，监视“订单”表的“订单日期”列，使其不能被“update”.


create trigger trigger_订单_update
on 订单
after update
as
    if update(订单日期) 
    begin
        raiserror('订单日期不能手动修改',10,1) 
        rollback transaction
    end
10.5 在“订单”表建立一个插入触发器，保证向“订单”表插入的货品必须要在“商品”表中一定存在。


create trigger trigger_订单_insert3
on 订单
after insert
as
    if (select count(*) from 商品, inserted where 商品.pid = inserted.pid)=0 
    begin
        print '商品不存在'
        rollback transaction
    end
10.6 “订单”表建立一个插入触发器，保证向“订单”表插入的货品信息要在“订单日志”表中添加


alter trigger trigger_订单_insert
on 订单 
for insert
as
    insert into 订单日志 select inserted.Id, inserted.pid,inserted.数量 from inserted

========

SQL触发器经验详解

触发器的介绍
1
触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，当对一个表进行操作（ insert，delete， update）时就会激活它执行，触发器经常用于加强数据的完整性约束和业务规则等。


触发器的分类
1
DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。
2
DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。
3
登陆触发器：是指当用户登录SQL SERVER实例建立会话时触发。


DML触发器介绍
1
在SQL SERVER 2008中，DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中，我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后，它们也就会被自动删除：INSERED


表用于存放你在操件insert、update、delete语句后，更新的记录。比如你插入一条数据，那么就会把这条记录插入到INSERTED表：DELETED表用于存放你在操作 insert、update、delete语句前，你创建触发器表中数据库。
2
触发器可通过数据库中的相关表实现级联更改，可以强制比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列，例如触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作。触发器也可以根据数据修


改前后的表状态，再行采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。
3
与此同时，虽然触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用？过多触发器会造成数据库及应用程序的维护困难，同时对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。


触发器步骤详解
1
首先，我们来尝试创建一个触发器，要求就是在AddTable这个表上创建一个Update触发器，语句为：
create trigger mytrigger on AddTable
for update


2
然后就是sql语句的部分了，主要是如果发生update以后，要求触发器触发一个什么操作。这里的意思就是如果出现update了，触发器就会触发输出：the table was updated!


3
接下来我们来将AddTable表中的数据执行一个更改的操作：


4
执行后，我们会发现，触发器被触发，输出了我们设置好的文本：


5
那触发器创建以后呢，它就正式开始工作了，这时候我们需要更改触发器的话，只需要将开始的create创建变为alter，然后修改逻辑即可：


6
如果我们想查看某一个触发器的内容，直接运行：exec sp_helptext [触发器名]


7
如果我想查询当前数据库中有多少触发器，以方便我进行数据库维护，只需要运行： select * from sysobjects where xtype='TR'


8
我们如果需要关闭或者开启触发器的话，只需要运行：
disable trigger [触发器名] on database --禁用触发器
enable trigger [触发器名] on database --开启触发器


9
那触发器的功能虽大，但是一旦触发，恢复起来就比较麻烦了，那我们就需要对数据进行保护，这里就需要用到rollback数据回滚~


10
第九步的意思就是查询AddTable表，如果里面存在TableName=newTable的，数据就回滚，触发器中止，那我们再进行一下测试，对AddTable表进行更改，发现，触发update触发器之后，因为有数据保护，触发器中止：


注意事项
禁用和开启触发器都需要一定的权限，如果权限不够是无法进行操作的。


========

触发器说明

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `qqfs_db_gm`.`a` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `qqfs_db_gm`.`<Table Name>`
    FOR EACH ROW BEGIN


    END


根据以上语法，做几点解释！


在游戏行业，所面对的数据，都是高并发，读写频繁，但是数据并不一定海量！这个时候，我们对性能的要求非常高！触发器，对性能影响过大！所以，在我们的几个项目中，我从没有引入触发器！不过对于小数据，或者是低并发、修改不频繁的应用，适当的引入触发


器还是可以行的！


DEFINER：触发器的创建者，可以手动指定创建者，默认为当前用户


BEFORE/AFTER：BEFORE，就是在触发的动作之前执行你所想要执行的语句(begin后面的sql语句);AFTER,在触发动作之后执行你所想要的语句！


INSERT/UPDATE/DELETE:你所要触发的条件。分别是insert、update、delete。这里不单单的针对这三个关键字，同样的有同类效果的语句也会触发！如LOAD DATA和REPLACE语句也会触发insert


`qqfs_db_gm`.`<Table Name>`：触发的对象都是表


语法解释非常简单！需要注意以下几点：
1.触发器里面不允许执行动态语句！或者是执行带动态语句的存储过程、存储函数等！


2.对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序(官方说明


)


3.触发的对象和触发执行的语句不能为同一个表！如：触发器a触发b表的insert操作，然后在b表执行insert操作，会报错！这里应该是触发器的一种强制的锁机制！所以就带有很大的性能损耗！


4.不能让两个触发器触发两个表，并使之循环！如：触发器a触发b表的insert操作，并往d表insert数据；触发器c触发d表的insert操作，并往表b insert数据；这样是不行的！会报错如下：Can't update table 'test' in stored function/trigger because it is 


already used by     statement which invoked this stored function/trigger.(第三点也会报这样的错误！)


5.触发器中有new和old关键字。old是指老的数据，很容易想到，会产生老数据的是update和delete操作，因为insert之前没有旧数据和该insert有直接关系！new关键字是指新数据，同理可知道，delete操作是没有新数据产生的，所有new关键字使用的触发条件是insert


和update！其实从这里可以看出，触发器又保存了两份数据，结合上面的对表的触发锁定，触发器的性能是不敢恭维的！


6.如果要在触发update的同时，修改该数据加1！用传统的方法update table set id = old.id + 1;这样肯定是不行的，违背了第三点，会报错：Can't update table 'test' in stored function/trigger because it is already used by statement which invoked 


this stored function/trigger.注意，这个错在触发器编译的时候，是能够通过的，但是在更新的时候，数据库会抛这个异常！
如果一定要这样做，那该怎么办呢？借助关键字new和old.
SET new.id = new.id + 1;


========

9 触发器要点

SQL触发器

 触发器是一种特殊的存储过程，它在表的数据变化时发生作用。
 触发器可以维护数据完整性。
 触发器在数据库里以独立的对象存储。
 存储过程通过其他程序来启动运行，触发器是由一个事件来启动运行。
 触发器不能接收参数。
 SQL支持3种类型的触发器：插入，更新，删除。
 触发器执行环境还包括一个或两个测试表，称之为INSERTED表和DELETED表。
 测试表是虚表，用于保存目标表更新、插入或删除的数据信息。
 Sql Server 2000及其以后的版本支持两种类型的触发器：AFTER触发器，INSTEAD OF触发器。
 AFTER触发器即为前面介绍的UPDATE、INSERT、DELETE触发器；INSTEAD OF触发器表示并不执行其所定义的操作(INSERT，UPDATE,DELETE)，而仅是执行触发器本身。