SQLServer触发器创制,数据库之触发器

壹,触发器概念 

      触发器(trigger)是SQL server
提需求程序员和数码分析员来保险数据完整性的一种方法,它是与表事件相关的特别的存储进程,它的施行不是由程序调用,也不是手工运行,而是由事件来触发,比如当对2个表举行操作(
insert,delete,
update)时就会激活它实施。触发器寻常用来进步数据的完整性约束和业务规则等。
触发器可以从 DBA_TRIGGERS ,USER_T安德拉IGGE路虎极光S
数据字典中查到。SQL3的触发器是二个能由系统活动执行对数据库修改的话语。

一:
触发器是一种奇特的积存进度﹐它无法被显式地调用﹐而是在往表中插入记录﹑更新记录或许去除记录时被活动地激活。所以触发器可以用来兑现对表实施复杂的完整性约束。

(1)触发器(trigger)是SQL server
提须要程序员和数目分析员来保险数据完整性的一种艺术,它是与表事件相关的分裂经常的积存进程,它的履行不是由程序调用,也不是手工运转,而是由事件来触发,比如当对三个表展开操作(
insert,delete,
update)时就会激活它执行。触发器经常用来提升数据的完整性约束和工作规则等。
触发器可以从 DBA_TRIGGERS ,USER_TRubiconIGGE奥迪Q3S
数据字典中查到。SQL3的触发器是3个能由系统活动执行对数据库修改的说话。
 –摘自百度完善

     
触发器能够查询其余表,而且可以蕴含复杂的SQL话语。它们主要用来强制坚守复杂的事体规则或要求。例如:您可以依照客户当前的帐户状态,控制是不是允许插入新订单。

触发器1_概念

二: SQL
Server为各样触发器都创设了八个专用表:Inserted表和Deleted表。那五个表。

(2)触发器与仓储进程的绝无仅有差异是触发器不可以执行EXECUTE语句调用,触发器为自行触发。

触发器也可用以强制引用完整性,以便在七个表中加上、更新或删除行时,保留在这一个表之间所定义的涉嫌。但是,强制引用完整性的最好办法是在相关表中定义主键和外键约束。尽管采纳数据库关系图,则足以在表之间创制关系以活动创造外键约束。

触发器的风味:

一:
触发器是一种特别的贮存进度﹐它无法被显式地调用﹐而是在往表中插入记录﹑更新记录或许去除记录时被自动地激活。所以触发器可以用来完毕对表实施复杂的完整性约`束。

贰,创设不难触发器:

   
 触发器与仓储进度的唯一不相同是触发器无法执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行。

1、触发器是在对表进行增、删、改时,自动执行的存储进度。触发器常用来强制业务规则,它是一种高级约束,通过事件展开接触而被执行。

二: SQL
Server为各类触发器都创设了三个专用表:Inserted表和Deleted表。那三个表由系统来维护﹐它们存在于内存中而不是在数据库中。那四个表的构造总是与被该触发器作用的表的协会同样。触发器执行
完毕后﹐与该触发器相关的那八个表也被删除。
Deleted表存放由于举办Delete或Update语句而要从表中删除的保有行。
Inserted表存放由于进行Insert或Update语句而要向表中插入的富有行。
SQLServer触发器创制,数据库之触发器。三:Instead of 和 After触发器 SQL Server两千提供了二种触发器:Instead of 和After
触发器。那二种触发器的差距在于他们被激活的同:

create trigger [dbo].[aa]
on [dbo].[aaaa]
for insert ,delete,update
as
truncate table bbbb
insert into bbbb
select code,imagepath,maxscore from aaaa

     查询数据库中持有触发器:select   *   from   sysobjects   where  
xtype=’TR’

2、触发器是二个出奇的事体单元,可以引用其他表中的列执行特殊的业务规则或数额逻辑关系。当出现错误时,可以举办rollback
transaction操作将总体触发器以及触发它的T-SQL语句一并回滚(不需出示阐明begin
transaction)。

Instead of触发器用于代替引起触发器执行的T-SQL语句。除表之外﹐Instead of
触发器也可以用来视图﹐用来扩展视图可以辅助的立异操作。

–其中 for  可换为 after 、before,依照实际情状

1、语法

3、各类触发器将采纳的两个临时表

After触发器在三个Insert,Update或Deleted语句之后执行﹐举行约束检查等动作都在After触发器被激活在此之前暴发。After触发器只可以用来表。

4、删除触发器

create trigger [shema_name . ] trg_name
on { table | view }
[ with encryption ]
{ for | after | instead of }
{ insert , update , delete }
as
sql_statement

   deleted
权且表:用于目前存放被删去的记录行副本(包蕴delete和update语句所影响的数据行);
                 
注意:被剔除的记录行,首先从原始表中剔除,并保存到触发器表。然后从触发器表中删除,再保存到deleted表。

多个表或视图的每一个改动动作(insert,update和delete)都得以有三个instead
of 触发器﹐贰个表的各个修改动作都足以有多少个After触发器。
四:触发器的实践进度
一经二个Insert﹑update或然delete语句违反了束缚﹐那幺After触发器不会履行﹐因为对约束的反省是在After触发器被拨动此前暴发的。所以After触发器无法超过约束。

 drop trigger triggerName

insert触发器实例

   inserted一时半刻表:用于权且存放插入的记录行副本(包蕴insert和update语句所影响的数据行);

Instead of
触发器可以取代激发它的操作来推行。它在Inserted表和Deleted表刚刚确立﹐别的任何操作还未曾发生时被执行。因为Instead
of 触发器在约束此前实施﹐所以它可以对约束举办一些预处理。

五,查找当前具备触发器

create trigger test
on al
for insert
as
declare @id int,@uid int,@lid int,@result char
select @id=id,@uid=uid,@lid=lid,@result=result from inserted
if(@lid=4)
begin
   update al set uid=99 where id=@id
   print 'lid=4时自动修改用户id为99'
end

    deleted表和inserted表的特色:
    > 这八个表的表结构与该触发器作用的表相同;
    > 那多个表是逻辑表,并且由系统管理;
    >
那多少个表是动态驻留在内存中的(不是储存在数据库中),当触发器工作形成后,它们也被删除;
    >
那多少个表是只读的,即只可以使用select语句查看(用户不可以直接改动);

五:使用T-SQL语句来创设触发器

select * from Sysobjects where xtype = ‘TR’

update触发器实例

4、所创设的触发器(insert、delete、update)是在原表数据行已经修改形成后再触及。所以,触发器是在约束检查之后才实施。

基本语句如下:
create trigger trigger_name
on {table_name | view_name}
{for | After | Instead of }
[ insert, update,delete ]
as
sql_statement

–Sysobjects
 中保存了创办的目的的新闻,如表和触发器的,若查找当前有所创立的表 xtype
值为 U

create trigger test_update
on al
   for update
as
   declare @oldid int,@olduid int,@oldlid int,@newid int,@newuid int,@newlid int
   select @oldid=id,@olduid=uid,@oldlid=lid from deleted;
   select @newid=id,@newuid=uid,@newlid=lid from inserted
   if(@newlid>@oldlid)
   begin
   print 'newlid>oldid'
   rollback tran;
   end
   else
   print '修改成功'

何以时候利用触发器?

六:删除触发器:
基本语句如下:

6、打开&&关闭触发器

delete触发器实例

a、一往无前主外键关系所不能担保的纷纷参照完整性和数量的一致性。
    不过,通过“级联引用完整性约束”可以更管用地推行那么些改动。

drop trigger trigger_name

disable trigger tableName on triggerName    –关闭
enable trigger tableName on triggerName   –打开

create trigger test_delete
on al
for delete
as
declare @did int,@duid int,@dlid int
select @did=id,@duid=uid,@dlid=lid from deleted
if(exists(select * from list where @dlid=id))
begin
print ‘不能删除’
rollback tran;
end
else
print ‘删除成功’

b、幸免恶意或不当的 INSE索罗德T、UPDATE 以及 DELETE 操作,并强制执行比
CHECK 约束定义的限定越来越复杂的别样限制。
   > 与 CHECK
约束不一致(check约束只可以引用作者表中的列),DML触发器可以引用其余表中的列
   > 触发器可以落成具有约束的功能,但不必然是一流方案;
   > 触发器可以运用自定义音信和较为复杂的错误处理

七:查看数据库中已有触发器:
— 查看数据库已有触发器
use jxcSoftware
go
select * from sysobjects where xtype=’TR’

 

 

c、DML
触发器可以评估数据修改前后表的气象,并按照该差别接纳措施。

— 查看单个触发器
exec sp_helptext ‘触发器名’

d、一个表中的同三个修改语句的DML触发器,允许被八个例外的操作(INSERT、UPDATE
或 DELETE)来响应

八:修改触发器:

触发器的连串:

基本语句如下:
alter trigger trigger_name
on {table_name | view_name}
{for | After | Instead of }
[ insert, update,delete ]
as
sql_statement

insert 触发器;(略)
delete 触发器;(略)
update 触发器:在修改表中记录行或某列数据时触发执行;
留意:update(列)函数:完成检测某列是或不是被涂改。

九:相关示例:
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’
997755.com澳门葡京,rollback transaction
end

update 更新操作分为两步:
首先,“删除”更改前原有数据行:删除的固有数据行将复制到deleted一时表中;
然后,“插入”更改后的新数据行:插入新数据行到原始表,同时将新数据行保存到inserted权且表和触发器表中;

6:Orders表建立二个布署触发器,保证向Orders表插入的货品信息要在Order表中添加

创办触发器的注意点:
1、create trigger必须是批处理(go)的第壹条语句;

alter trigger addOrder
on Orders
for insert
as
insert into Order
select inserted.Id, inserted.goodName,inserted.Number from inserted

2、2个触发器语句只好用到1个表或二个视图中;
   on 表名/ 视图名

你大概感兴趣的篇章:

  • SQLServer二零零七触发器提醒其余会话正在利用工作的上下文的化解措施
  • SQL
    Server触发器及触发器中的事务学习
  • SQL Server 触发器
    表的特定字段更新时,触发Update触发器
  • sqlserver
    禁用触发器和启用触发器的言辞
  • sqlserver中触发器+游标操作落成
  • sqlserver 触发器教程
  • 应用Sqlserver事务公布落实数据同步(sql二零零六)
  • Sqlserver
    存储进度中结成工作的代码
  • SQLServer分布式事务难点
  • sqlserver中的事务和锁详细剖析
  • 浅析SQL
    Server中含有事务的贮存进度
  • SQL
    Server触发器和业务用法示例

3、贰个触发器语句可以实施三个操作;
   for delete,insert,update — 无先后顺序的肆意组合

4、指出DML触发器不回去任何结果。那是因为对那几个重临结果的非正规处理必须写入每种允许对触发器表展开修改的应用程序中。
     若要预防从 DML
触发器再次来到任何结果,请不要在触发器定义中隐含select语句或变量赋值;
     借使必须在触发器中举行变量赋值,则应当在触发器被触发在此之前使用set
nocount on语句以幸免重临任何结果集;

     注意:以往版本的SQL Server
中,将会去除从触发器重返结果集的功力。

5、如果“触发器表”本身也存在约束,则在执行insert、delete、update触发器前,首先会检讨“触发器表”上设有的自律。假如不满意约束,则不会举办其insert、delete、update触发器。

 

翻看当前数据库中的全体触发器

select * from sys.triggers

 

创造一时半刻表 #tableName

create table #tableName

哪些选取 SQL Server 触发器

 

触发器2_开头化环境SQL

997755.com澳门葡京 1997755.com澳门葡京 2初叶化环境

————— 开端化环境 —————

create database TriggerDatabase
use TriggerDatabase
go

if exists(select * from sysobjects where name=’bank’)
   drop table bank

create table bank — 账户新闻表
(
   userName      varchar(10) not null,  –顾客名
   cardID        varchar(10) not null,  –卡号
   currentMoney  money       not null   –当前余额
)

if exists(select * from sysobjects where name=’transInfo’)
   drop table transInfo

create table transInfo –交易新闻表
(
   cardID     varchar(10) not null,  –卡号
   transType  char(4)     not null,  –交易类型(存入/支取)
   transMoney money       not null,  –交易金额
   transDate  datetime    not null   –交易日期
)
go

————— 添加封锁 —————
alter table bank
add constraint CK_currentMoney check(currentMoney>=1);

alter table transInfo
add constraint DF_transDate default(getdate()) for transDate;

alter table transInfo
add constraint CK_transType check(transType in(‘支取’,’存入’));

————— 添加测试数据 —————
/* 张三 1000元 */
insert into bank(userName,cardID,currentMoney)
        values(‘张三’,’1001 0001′,1000);
/* 李四 1元 */
insert into bank(userName,cardID,currentMoney)
        values(‘李四’,’1001 0002′,1);
/* 张三 支取 200元 */
insert into transInfo(cardID,transType,transMoney)
        values(‘1001 0001′,’支取’,200);

————— 查看结果 —————
select * from bank;
select * from transInfo;
go

触发器3_概念触发器的格式

997755.com澳门葡京 3997755.com澳门葡京 4概念触发器的格式

— =============================================
— Author:        xugang
— Create date: 2010-2-14
— Description:    定义触发器的凝练格式
—  [ ]:可选     { }必选
— =============================================

create trigger [ schema_name. ] — 触发器所属架构
               trigger_name     — 触发器名称
on { table | view }       — 触发器的表或视图
   [ with encryption ]    — 加密dml触发器定义(前边详解)
{ for | after } 
   /* after:只有在接触它的SQL语句执行成功后才能振奋。
             (只能对“表”定义after) */
    { insert,update,delete } 
as
    /* SQL语句… */
go

–查看当前数据库中的全部触发器
select * from sys.triggers

触发器4_insert 触发器SQL

997755.com澳门葡京 5997755.com澳门葡京 6insert 触发器

—————— insert 触发器 ——————
use TriggerDatabase
go
if exists(select * from sysobjects 
           where name=’trig_insert_transInfo’)
drop trigger trig_insert_transInfo
go

— create trigger必须是批处理(go)的首先句

create trigger trig_insert_transInfo
on transInfo for insert
as
    declare @_transType   char(4),  –定义变量
            @_transMoney  money,
            @_cardID      char(10),
            @balance      money     –所剩余额

    — 从inserted一时表中收获记录值
    select @_transType = transType,
           @_transMoney = transMoney,
           @_cardID = cardID 
           from inserted

    if(@_transType = ‘支取’)
       update bank set currentMoney=currentMoney-@_transMoney
              where cardID = @_cardID;
    else 
       update bank set currentMoney=currentMoney+@_transMoney
              where cardID = @_cardID;

    –呈现交易金额
    print ‘交易成功! 交易金额:’
          + convert(varchar(20),@_transMoney)

    –展现所剩余额
    select @balance = currentMoney from bank 
           where cardId = @_cardID

    print ‘卡号:’+@_cardID 
          + ‘ 余额:’+convert(varchar(20),@balance);
go

—————— 测试触发器 ——————

— delete from transInfo
set nocount on –不出示T-SQL影响的记录行数

insert into transInfo(cardID,transType,transMoney)
             values(‘1001 0001′,’支取’,200);
insert into transInfo(cardID,transType,transMoney)
             values(‘1001 0001′,’存入’,10000);
–查看结果
select * from bank
select * from transInfo

触发器5_delete 触发器SQL

997755.com澳门葡京 7997755.com澳门葡京 8delete 触发器

/* 落成: 当清除’交易消息表’的多寡时,
         自动备份被免除的数目到backupTable表中
*/

—————— delete 触发器 ——————
use TriggerDatabase
go

if exists (select * from sysobjects 
           where name=’trig_delete_transInfo’)
drop trigger trig_delete_transInfo
go

create trigger trig_delete_transInfo
on transInfo after delete  —  for | after
as
   print ‘起先备份数据,请稍后……’
   — 即便数据库中,不设有 backupTable 表
   if not exists(select * from sysobjects 
                  where name=’backupTable’)
      select * into backupTable from deleted –deleted临时表
   else
      insert into backupTable select * from deleted
  
   print ‘备份成功,备份表 backupTable 中的数据为:’
        select * from backupTable;
go

—————— 测试触发器 ——————
set nocount on

delete from transInfo; –测试

–查看结果
select * from transInfo
select * from backupTable

触发器6_update 触发器SQL

997755.com澳门葡京 9997755.com澳门葡京 10update 触发器

—————— update 触发器 ——————
use TriggerDatabase
go

if exists (select * from sysobjects 
           where name=’trig_update_bank’)
drop trigger trig_update_bank
go

create trigger trig_update_bank
on bank for update  –在bank表上创建update触发器
as
   declare @beforeMoney money,
           @afterMoney  money,
           @currentTrans money –当前交易金额

   –从deleted暂且表,获取交易前的余额
   select @beforeMoney = currentMoney from deleted;
   –从inserted一时表,获取交易后的余额
   select @afterMoney = currentMoney from inserted;
   
   if abs(@afterMoney-@beforeMoney) > 2000
      begin
        print ‘当前贸易金额为:’ +
              convert(varchar(20),abs(@afterMoney-@beforeMoney))
        — 自定义错误消息
        raiserror(‘每便交易金额无法超过3000元,交易失利!’,16,1)
       
        rollback transaction –回滚事务,撤除交易!
        /* 注意:
           触发器是1个分外的政工单元
           不需出示评释begin transaction
        */
      end
go

—————— 测试触发器 ——————
set nocount on

–测试1: 在 bank表触发 update触发器
update bank set currentMoney = currentMoney + 25000
       where cardID = ‘1001 0001’

–测试2: 通过 transInfo表的 trig_insert_transInfo触发器
—             直接触发 bank表的 trig_update_bank触发器

insert into transInfo(cardID,transType,transMoney)
             values(‘1001 0001′,’存入’,10000);

–查看结果
select * from bank
select * from transInfo

 

触发器7_MSDN参考

加密 dml触发器定义
      若要确保其他用户无法查看触发器定义,可以使用with
encryption子句加密 dml 触发器。
      使用with
encryption子句后,触发器定义即以不可以读取的格式进行仓储。
     
触发器定义加密后,不可以进展解密。且任哪个人都爱莫能助开展查看,蕴涵触发器的持有者和系统管理员。

update() 函数:
可用来明确 insert或 update语句是或不是影响表中的特定列。
不管几时为列赋值,该函数都将赶回 true。

运用if update() 子句示例:

997755.com澳门葡京 11997755.com澳门葡京 12if update()子句示例

  create table testTable(a int null, b int null)
  go

  create trigger my_trig
  on testTable for insert
  as
     if update(b)
     print ‘列b已被改动!’
  go
 
  insert into testTable(b) values(123);

  — drop table testTable

注意:  
      由于 delete 语句无法只对某列举办删减,
      由此无法将if update()子句应用于delete 语句。

 

columns_updated() 函数:
也可用以检查 insert或 update语句更新了表中的怎样列。
此函数使用整数位掩码指定要测试的列。

使用columns_updated() 函数示例:

997755.com澳门葡京 13997755.com澳门葡京 14columns_updated()函数示例

  create table testTable2(a int null, b int null)
  go

  create trigger my_trig2
  on testTable2 for insert
  as
     if ( columns_updated() & 2 = 2 )
     print ‘列b已被修改!’
  go

  insert into testTable2(b) values(123);
 
  — drop table testTable2

MSDN参考来源:
      对 DML 触发器举办编程

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website