SQL Server中触发器的简单使用示例

1、定义触发器

CREATE  TRIGGER  <触发器名>

ON  <表名>  FOR|AFTER|INSTEAD OF  <触发事件>

AS

<触发动作体>

注:

  1. AFTER表示只有执行了指定的操作(DELETE,UPDATE,INSERT)之后,触发器才被激活,执行触发器中的sql语句;
  2. FOR表示为AFTER触发器,并且该触发器只能在表上创建;
  3. INSTEAD OF指定触发器类型为INSTEAD OF,通常用于使得不能直接更新的视图进行更新。

2、关于触发器

各种类型用到的测试表包括DELETED表 及INSERTED表。当执行DELETE触发器时会将触发器对应的表中的记录移到DELETED表,可以从DELETED表得到删除的记录;当执行INSERT触发器会将触发器对应的表中的增加新行,同时也会向INSERTED表中增加相同的行;如果执行UPDATED触发器会将“旧”行复制到DELETED表中,然后将新行同时插入到触发器对应 表及INSERTED表中。

 

3、INSERT触发器

【例一】

create trigger insert_trigger

on student for insert

as

begin

update teachers set total = total + 1

end

说明:向表student插入记录时候便会使得表teachers表中的total字段增一.

 

4、DELETE触发器

【例1】

as

begin

declare @rowCount int

select @rowCount=@@ROWCOUNT /*@@ROWCOUNT为全局变量,记录受影响的行数*/

if @rowCount>1

begin

rollback transaction

raiserror('当前删除的记录数为%d,一次仅允许删除一行数据',16,1,@rowCount)

end

end

 

delete from student where name like '李%'

错误信息为:

5、UPDATE触发器

创建一个表记录更新操作的日志信息,

create table logInfo(

oper_time datetime,

old_value int,

new_value int

);

 

create trigger S_update

on Student for update

as

begin

if(COLUMNS_UPDATED()&3)>0   /*第1列或第2列*/

begin

rollback transaction

raiserror('姓名及学号不允许修改',16,1)

return

end

if(COLUMNS_UPDATED() &4=4)

begin

print '修改了性别'

return

end

 

declare @old_value int,@new_value int

select @old_value=age from DELETED

select @new_value=age from INSERTED

insert into logInfo values(GETDATE(),@old_value,@new_value)

end

 

 

 

执行语句update student set age=29 where name='张琴'

可以看到logInfo表中多了一行记录,

注:COLUMNS_UPDATED()方法获得更新的列,例如如果返回3标识更新了第1列和第2列,将更新的列按照二进制形式标识,更新标记为1。符号“&”为按位与运算,COLUMNS_UPDATED() & 3>0表示更新了第1列或者第2列(3的二进制形式最低二位全为1,其它位为0),COLUMNS_UPDATED() & 5=5表示更新了第1列及第3列。

 

6、INSTEAD OF触发器

INSTEAD OF触发器使得不可更新的视图变得可以进行更新操作。例如有三张表S1,S2,S3,结构都相同,如下:

现在建立一个视图,查询这三张表中的所有数据。表S1存储张三的记录,表S2存储李四的记录,表S3存储王武的记录。视图建立如下,

create view S_all

as

(

select * from S1

union all

select * from S2

union all

select * from S3

)

 

创建触发器

create trigger trigger_s

on S_all instead of insert

as

begin

declare @name nvarchar(10)

select @name=用户 from INSERTED

if @name='张三'

insert into S1 select * from INSERTED

else if @name='李四'

insert into S2 select * from INSERTED

else if @name='王武'

insert into S3 select * from INSERTED

else

print '输入姓名错误'

end

 

向视图S_all插入记录

insert into S_all values('张三','笔记本','是')

可以看到该记录插入到了表S1中。

  • 5
    点赞
  • 17
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 2
    评论
sqlserver触发器例子 一﹕ 触发器是一种特殊的存储过程﹐它不能被显式地调用﹐而是在往表插入记录﹑更新记录或者删除记录时被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约`束。 二﹕ 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 六﹕相关示例﹕ 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 tri
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

数据之道

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值