Sql Server触发器使用方法详解

时间：2022-06-29 08:04:04 编辑：袖梨来源：一聚教程网

SQL触发器实例1

定义：何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储过程。
常见的触发器有三种：分别应用于Insert , Update , Delete 事件。

我为什么要使用触发器？比如，这么两个表：

代码如下

复制代码

      Create Table Student(              --学生表
        StudentID int primary key,       --学号
        ....
       )

      Create Table BorrowRecord(               --学生借书记录表
        BorrowRecord   int identity(1,1),       --流水号
        StudentID      int ,                    --学号
        BorrowDate     datetime,                --借出时间
        ReturnDAte     Datetime,                --归还时间
        ...
      )

     用到的功能有:
        1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);
        2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。
     等等。

这时候可以用到触发器。对于1，创建一个Update触发器：

代码如下

复制代码

     Create Trigger truStudent
       On Student                         --在Student表中创建触发器
       for Update                          --为什么事件触发
     As                                        --事件触发后所要做的事情
       if Update(StudentID)
       begin

         Update BorrowRecord
           Set StudentID=i.StudentID
           From BorrowRecord br , Deleted   d ,Inserted i      --Deleted和Inserted临时表
           Where br.StudentID=d.StudentID

end

     理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。
     一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：
                             虚拟表Inserted                     虚拟表Deleted

在表记录新增时     存放新增的记录                         不存储记录
         修改时           存放用来更新的新记录                   存放更新前的记录
         删除时           不存储记录                             存放被删除的记录

一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。

对于2，创建一个Delete触发器

代码如下	复制代码
Create trigger trdStudent On Student for Delete As Delete BorrowRecord From BorrowRecord br , Delted d Where br.StudentID=d.StudentID

从这两个例子我们可以看到了触发器的关键：A.2个临时的表；B.触发机制。

实例2

代码如下

复制代码

select * from sysobjects where xtype='TR'
--查看有哪些触发器
CREATE TRIGGER Ma_Importance_INSERT
ON qyml
FOR INSERT/*INSERT,UPDATE,DELETE*/
AS
begin
declare @InsertID int
select @InsertID=id from INSERTED--查找被添加的一条记录内容
insert into Ma_Importance(cid,ichar) values(@InsertID,'d')
end

CREATE TRIGGER Ma_Importance_DELETE
ON qyml
FOR DELETE/*INSERT,UPDATE,DELETE*/
AS
begin
declare @deleteID int
select @deleteID=id from DELETED--查找被删除的一条记录内容
delete from Ma_Importance where cid=@deleteID
end

drop TRIGGER ma_inportance_insert

附后一篇触发器的读写创建删除

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

二? SQL Server为每个触发器都创建了两个专用表?Inserted表和Deleted表。这两个表。

二? 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触发器使用方法详解

相关文章

热门栏目

php教程

asp.net教程

手机开发

css教程

网页制作

办公数码

jsp教程