澳门新萄京:多少个表上的多少个触发器实践顺
分类:数据库

SQL Server “复制”表结构,创建_Log表及触发器

实例效果:

完毕表数据的增修删时,记录日志。

1.“复制”现有表,

   创造相应的_Log表;

 (注意点:

通过select union all 的方式,避免了IDENTITY 的“复制”,
即如果原表有 PK 如 ID Identity,_Log表 仅“复制”ID int,“不复制” Identity属性,
以便 Insert Update Delete时,可以Insert到Log表。)

2.对现存表,成立Insert,Update,Delete的触发器,

  并将相应数据 记录到对应的_Log表

BEGIN TRAN   
BEGIN TRY  


--定义TAB_CURSOR
DECLARE TAB_CURSOR CURSOR read_only
FOR
   SELECT name FROM SysObjects Where XType='U' 
  -- AND name = N'T01ConstItem' 
  and [name] <> N'dtproperties'
   ORDER BY Name;

--打开
OPEN TAB_CURSOR

DECLARE @P_TabName NVARCHAR(200);
DECLARE @P_TabName_Log NVARCHAR(200);
DECLARE @P_Create_Log_Tab NVARCHAR(4000);
DECLARE @P_Create_Trig_I NVARCHAR(4000);
DECLARE @P_Create_Trig_U NVARCHAR(4000);
DECLARE @P_Create_Trig_D NVARCHAR(4000);

FETCH NEXT FROM TAB_CURSOR 
           INTO @P_TabName
--循环
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
    BEGIN   
    SET @P_TabName_Log = CONCAT(@P_TabName,N'_Log');

    SET @P_Create_Log_Tab = N' SELECT * ';
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' INTO ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName_Log );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' UNION ALL ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' SELECT TOP (1) * ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,N''I '' AS Action');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' ,Getdate() AS ActionDate ');
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,N' FROM  ' );
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab,@P_TabName);
    SET @P_Create_Log_Tab = CONCAT(@P_Create_Log_Tab, N' WHERE 1=0 ; ');

    EXEC( @P_Create_Log_Tab);

    --SET @P_Create_Log_Tab = CONCAT(N' SET IDENTITY_INSERT ',@P_TabName_Log ,' ON '); 
    --EXEC( @P_Create_Log_Tab);


    SET @P_Create_Trig_I = N' create trigger ';
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' trig_',@P_TabName,N'_I ');
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' on ',@P_TabName,N' after INSERT as ');
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N' begin ');    
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I,N' select * , N''I'',Getdate() from Inserted ; ' );
    SET @P_Create_Trig_I = CONCAT(@P_Create_Trig_I, N'end ');

    --select @P_Create_Trig_I;

    EXEC( @P_Create_Trig_I);

    SET @P_Create_Trig_U = N' create trigger ';
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_U ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after UPDATE as ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UD'',Getdate() from Deleted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''UI'',Getdate() from Inserted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
    EXEC( @P_Create_Trig_U);

    SET @P_Create_Trig_U = N' create trigger ';
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' trig_',@P_TabName,N'_D ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' on ',@P_TabName,N' after DELETE as ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N' begin ');
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'insert into ',@P_TabName_Log );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U,N' select * , N''D'',Getdate() from Deleted ; ' );
    SET @P_Create_Trig_U = CONCAT(@P_Create_Trig_U, N'end ');
    EXEC( @P_Create_Trig_U);

    END
   FETCH NEXT FROM TAB_CURSOR INTO @P_TabName
END

--关闭
CLOSE TAB_CURSOR
--释放
DEALLOCATE TAB_CURSOR

COMMIT TRAN;  

END TRY  
BEGIN CATCH  
    SELECT ERROR_MESSAGE() AS ErrorMessage  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ROLLBACK TRAN;  
END CATCH

 

转发来源于:

  一声不响讲到触发器了,日常我们做程序的少之又少接触到触发器,触发器的操作日常是DB人士来实现。

我们这里介绍多个不落窠臼的表,Inserted表和Deleted表。此二表仅仅在触发器运维时存在。你可以动用该四个表来正确地分明触发触发器的动作对数据表所做的改进。比如,通过检查Deleted表,你能够分明那几个记录由某一动作删除。盘算上边包车型大巴事例:

触发器使用  
  能够定义一个无论什么日期用INSERT语句向表中插入数据时都会实行的触发器。    
  当触发INSERT触发器时,新的数据行就能被插入到触发器表和inserted表中。inserted表是一个逻辑表,它饱含了早就插入的数据行的一个别本。inserted表包罗了INSERT语句中已记录的插入动作。inserted表还允许引用由伊始化INSERT语句而产生的日记数据。触发器通过检查inserted表来鲜明是否施行触发器动作或怎样实践它。inserted表中的行总是触发器表中生机勃勃行或多行的别本。    
  日志记录了有着改善数据的动作(INSERT、UPDATE和DELETE语句),但在作业日志中的音讯是不足读的。但是,inserted表允许你援引由INSERT语句引起的日记变化,那样就能够将插入数据与产生的成形实行相比较,来证实它们或接收越来越动作。也能够直接援引插入的多少,而不自然它们存款和储蓄到变量中。  
   
   
    DELETE触发器的专门的学业进度    
  当触发DELETE触发器后,从受影响的表中删除的就要被停放到多少个极其的deleted表中。deleted表是三个逻辑表,它保留已被删去数据行的叁个别本。deleted表还允许援引由开端化DELETE语句爆发的日志数据。    
  使用DELETE触发器时,须求思考以下的事项和标准:    
                     当某行被加多到deleted表中时,它就不再存在于数据库表中;由此,deleted表和数量库表没有雷同的行。    
                     创设deleted表时,空间是从内部存款和储蓄器中分配的。deleted表总是被储存在高速缓存中。    
                     为DELETE动作定义的触发器并不实践TRUNCATE   TABLE语句,原因在于日志不记录TRUNCATE   TABLE语句。    
   
   
      INSTEAD   OF触发器的干活历程    
  能够在表或视图上点名INSTEAD   OF触发器。实施这种触发器就可以取代原始的触发动作。INSTEAD   OF触发器扩大了视图更新的品种。对于每黄金年代种触发动作(INSERT、UPDATE或   DELETE),每八个表或视图只好有三个INSTEAD   OF触发器。    
  INSTEAD   OF触发器被用于更新这么些未有主意通过正规方式立异的视图。举例,平时不可能在一个依照连接的视图上实行DELETE操作。不过,能够编制几个INSTEAD   OF   DELETE触发器来兑现删除。上述触发器能够访谈这些假若视图是一个的确的表时已经被去除的数据行。将被去除的行存款和储蓄在多个名字为deleted的干活表中,就好像AFTEOdyssey触发器相像。相像地,在UPDATE   INSTEAD   OF触发器只怕INSERT   INSTEAD   OF触发器中,你能够访谈inserted表中的新行。    
  无法在蕴藏WITH   CHECK   OPTION定义的视图中创制INSTEAD   OF触发器。    
   
   
  UPDATE触发器的职业进度    
  可将UPDATE语句看成两步操作:即捕获数据前像(before   image)的DELETE语句,和破获数据后像(after   image)的INSERT语句。当在概念有触发器的表上实践UPDATE语句时,原始行(前像卡塔 尔(英语:State of Qatar)被移入到deleted表,更新行(后像卡塔尔被移入到inserted表。    
  触发器检查deleted表和inserted表以至被更新的表,来鲜明是或不是更新了多行以致如何进行触发器动作。    
  能够利用IF   UPDATE语句定义二个蹲点钦点列的数量更新的触发器。那样,就足以让触发器轻松的隔开出一定列的位移。当它检查评定到钦定列已经更新时,触发器就能越发实行适当的动作,举个例子发出错误音信提议该列无法更新,大概依照新的翻新的列值奉行业作风流洒脱雨后春笋的动作语句。  

  可是有个别时候有的轻便的职业要求大家和煦去做到,不能够每趟都去麻烦DB人士,所以说,编制程序职员要全才,除了编制程序感到的专门的学问知识也要读书,譬如js,css,html,t-sql等局地语法,不必然要去领悟,不过要熟知,起码语法可以看懂,那样对大家的编制程序有经济的作用,现身非凡错误,大家也实惠调节和测验,以便最快搜索错误。

CREATE TRIGGER tr_webusers_delete ON webusers

  1. 即有四个触发器   tg1,tg2,tg3  
      都是update触发  
      系统是规行矩步名称排序tg1->tg2->tg3的相继触发,照旧同失常候触发? 

  言归正传,什么事触发器,看名就能猜到其意义,就是您做三个操作,就能够触发另二个事件,去实行一些操作。

FOR DELETE

钦命第二个和终极叁个触发器  
  可将与表相关联的   AFTE凯雷德   触发器之一钦定为每一种   INSERT、DELETE   和   UPDATE   触发动作施行的率先个或最终二个   AFTE福特Explorer   触发器。在第2个和末段贰个触发器之间激发的   AFTEHighlander   触发器将按未定义的逐风流洒脱施行。  
   
  若要钦点   AFTELacrosse   触发器的次第,请使用   sp_settriggerorder   存款和储蓄进度。可用的选项有:    
   
  第一个    
  钦定该触发器是为触发操作激发的首先个   AFTE索罗德   触发器。  
   
  最终三个    
  钦命该触发器是为触发操作激发的最后叁个   AFTE奥迪Q5   触发器。  
   
  无    
  钦赐触发器的激发未有一定的次第。主要用来重新安装第二个或最终叁个触发器。  
   
  以下是接收   sp_settriggerorder   的示例:  
   
  sp_settriggerorder   @triggername   =   'MyTrigger',   @order   =   'first',   @stmttype   =   'UPDATE'  
   
   
   
  主要     第二个和终极一个触发器必需是八个例外的触发器。  
   
   
  大概还要在表上定义了   INSERT、UPDATE   和   DELETE   触发器。每个语句类型只怕都有谈得来的第八个和末段一个触发器,但它们无法是相符的触发器。  
   
  即使为某些表定义的首先个或最终三个触发器不包蕴触发操作,如   FO昂Cora   UPDATE、FOEscort   DELETE   或   FO途锐   INSERT,则缺乏的操作将还未有第多少个或最终贰个触发器。  
   
  不可能将   INSTEAD   OF   触发器钦赐为率先个或倒数触发器。在对底蕴表张开更新前激发   INSTEAD   OF   触发器。然则,假设由   INSTEAD   OF   触发器对功底表打开翻新,则那么些创新将产生于在表上定义触发器(包蕴率先个触发器卡塔 尔(阿拉伯语:قطر‎之后。比如,即便视图上的   INSTEAD   OF   触发器更新基表况兼该基表包含多个触发器,则该三个触发器在   INSTEAD   OF   触发器插入数据在此以前激发。有关越来越多音讯,请参见钦赐触发器几时激发。  
   
  借使   ALTE奥迪Q5   TRubiconIGGEENCORE   语句校勘了第四个或最后一个触发器,则将除了   First   或   Last   特性,并且顺序值将安装为   None;必需用   sp_settriggerorder   重新苏醒设置此顺序。  
   
  OBJECTPROPERTY   函数使用性质   ExecIsFirstTrigger   和   ExecIsLastTrigger   报告触发器的次第是第贰个依旧最终二个。  
   
  复制将为小编是即时更新订户或排队更新订户的任何表自动生成第三个触发器。复制须要它的触发器是率先个触发器。假使尝试使具有率先个触发器的表变为那时更新订户或排队更新订户,复制将吸引错误。假若使表变为当下更新订户或排队更新订户之后使客户定义触发器成为第八个触发器,则   sp_settriggerorder   会再次来到贰个漏洞非常多。假若在复制触发器上选用   ALTEHighlander,或使用   sp_settriggerorder   将复制触发器修改为最终触发器或无触发器,则订阅将不可能精确职业。  

  例如你点烟花,点是一个动作,烟花是另贰个动作,点动作下笔千言就能接触烟花这些动作。

AS

八个触发器完全雷同吗,借使整个肖似,会先实践后创设的触发器。试行的相继与创设的逐条相反。

  还会有正是触发器必得依附二个核心,比方借助于某一张表,就如编制程序中事件这一个定义。

INSERT weblog (activity) SELECT user_name FROM Deleted

  上边我们经过一个粗略的实例,和我们一步一步的来精晓和接受触发器。

该触发器自动地创设三个webusers表的笔录。当在webusers表内去除三个客商的姓名时,触发器会自动地把该姓名插入到weblog表中。假如你一相当大心执行了上面的话语:

  实例需求:

DELETE webusers

    1,建筑商品表(Store卡塔 尔(阿拉伯语:قطر‎,订单表(orders卡塔 尔(阿拉伯语:قطر‎,日志表(Logs卡塔 尔(英语:State of Qatar)

该语句会删除在webusers表内的装有记录。常常地,这么些记录会永远地不见,然则上边包车型大巴触发器会在有记录从webusers表中除去时自动的接触。该触发器会检查表Deleted来鲜明有那多少个在webusers表内的记录被去除,并且把持有删除了的笔录拷贝到weblog表中。

    2,创设订单表插入触发器,达成插入一条订单音讯,商品表中货品数量相应缩小,订单中的总金额相应增添。

为了还原那多少个意外被删去的记录,你能够运用INSERT和SELECT语句再贰回把它们从weblog表中拷贝到webusers表中。借使您无法允许意外省错失一条记下时,你能够选择方面包车型地铁不二秘诀来创设一个表内数据的备份。

    3,创立订单表更新触发器,达成立异一条订单新闻,商品表中货色数量相应改造,订单中的总金额相应退换。(和2形似卡塔尔

Deleted表和有记录被删除的表的列结构完全一样。在前面包车型客车事例内,Deleted表具备和webusers表相似的构造。

    4,创立日志表触发器,完结立异商品表价格变动情状。

前天只要你想追踪全数插入某一表格的笔录。比方,你想把每一条插入webusers表内的记录都在weblog表内做备份,你能够应用上面包车型客车触发器来实现该职务:

1,创制商品表(Store卡塔尔国,订单表(orders卡塔尔国,日志表(Logs卡塔 尔(阿拉伯语:قطر‎

  第一步没怎么疏解,大家创立表,并加一些演示数据。

  示例代码:

 1 create table Store
 2 (
 3     ID uniqueidentifier primary key,
 4     ProductID int not null,
 5     ProductPrice money not null default 1,
 6     ProductCH nvarchar(80) not null,
 7     ProductDate datetime not null,
 8     NowNumber int not null
 9 );
10 create table orders
11 (
12     OrderID int primary key,
13     ProductID int not null,
14     BuyNumber int Not null default 1,
15     BuyPricr money not null,
16     NowOrderPrice money default 0 
17 )
18 create table Logs
19 (
20     ID uniqueidentifier primary key,
21     operatedatetime datetime,
22     ProductID int,
23     oldprice money,
24     newprice money
25 );
26 
27 insert into dbo.Store
28 values(NEWID(),1001,5000,'联想','2011-9-1',50)
29 insert into dbo.Store
30 values(NEWID(),1002,6000,'apple','2011-9-1',50)
31 insert into dbo.orders(OrderID,ProductID,BuyNumber,BuyPricr)
32 values(10013,1001,5,6000)

CREATE TRIGGER tr_webusers_insert ON webusers

2,创立订单表触发器

  要求2,3雷同操作自个儿在联合签名讲授了。

  先看示例代码:

 1 create trigger tri_order_NowOrderPrice
 2 on orders after insert
 3 as
 4 begin
 5     declare @NowOrderPrice money;
 6     declare @BuyNumber int;
 7     declare @ProductID int;
 8     select @ProductID= ProductID,@BuyNumber= BuyNumber from inserted;
 9     select @NowOrderPrice=inserted.BuyNumber * inserted.BuyPricr from inserted;
10     update orders set NowOrderPrice=@NowOrderPrice where ProductID=@ProductID;
11     update Store set NowNumber=NowNumber-@BuyNumber where ProductID=@ProductID;
12 end;
13 
14 create trigger tri_store_NowOrderPrice2
15 on orders after update
16 as
17 begin
18     declare @NowOrderPrice money;
19     declare @ProductID int;
20     declare @BuyNumber1 int;
21     declare @BuyNumber2 int;
22     select @ProductID= ProductID from inserted;
23     select @BuyNumber1=BuyNumber from inserted;
24     select @NowOrderPrice=inserted.BuyNumber * inserted.BuyPricr from inserted;
25     select @BuyNumber2=deleted.BuyNumber from deleted where ProductID=@ProductID;
26     update orders set NowOrderPrice=@NowOrderPrice where ProductID=@ProductID;
27     update Store set NowNumber=NowNumber-(@BuyNumber1-@BuyNumber2) where ProductID=@ProductID;
28 end;

  触发器的重点字是trigger,语法是,on 表名 after 操作名称(平时为insert,update,delete卡塔尔,begin end中写一些触发器的处理操作。

  inserted获得insert之后的数据。语法正是如此轻便。

FOR INSERT

3,创制日志表触发器

  日志表触发器要做的操作正是记录商品价位变化,那对应就应有在商品表中创建触发器。

  示例代码:

 1 create trigger tri_NowOrderPrice4
 2 on dbo.Store after insert,update,delete
 3 as
 4 begin
 5     if exists(select *from inserted) and exists(select *from deleted)
 6         begin
 7             print('update');
 8             declare @price1 money;
 9             declare @price2 money;
10             declare @datetime datetime;
11             select @price1=ProductPrice from deleted;
12             select @price2=ProductPrice from inserted;
13             if @price1!=@price2
14                 begin
15                     declare @ProductID int;
16                     select @ProductID=ProductID from inserted
17                     insert into Logs(ID,operatedatetime,ProductID,oldprice,newprice)
18                     values(newid(),convert(datetime,getdate()),@ProductID,@price1,@price2)
19                     
20                     select *from dbo.Logs
21                 end
22         end
23     else if exists(select *from inserted)
24         begin
25             print('insert');
26         end
27     else if exists(select *from deleted)
28         begin
29             print('delete');
30         end
31     else
32         begin
33             print('others');
34         end
35 end

  触发器正是这么些剧情,有关触发器的局地复杂操作希望大家一时间商讨下,讲的糟糕请大家多多指正,希望大家学好t-sql语言。

  现在继续整治编制程序相关内容,希望我们多多关心。。。。

  

AS

INSERT weblog(activty) SELECT user_name FROM INSERTED

该触发器和后边的触发器极其雷同,除了以下两点以外:

该触发器在有记录插入表格webusers时接触;该触发器是FOPAJERO INSERT。

该触发器把记录从Inserted表拷贝到weblog表内。

Inserted表内饱含了具备曾经插入到表内的新记录。即使八个新客商的真名插入到webusers表内时,触发器会自行地把新的客户姓名从webusers表拷贝到weblog表内。

当你想使用三个粗略的表来记录全部产生在您数据库中三个万分重大的表的动作时,这种复制数据的方式足够有效。你能够运用该表来获取对你的数据库的位移的笔录,而且能够用来记录和确诊一些只怕产生的难点。

你相仿能够行使Inserted表和Deleted表来记录UPDATE对触发器所在的表所做的改观。当三个和触发器相关的表内的数目被涂改时,Deleted表满含了有着列在改正以前的值,而Inserted表包涵了装有列在改变之后的值。参看下边包车型地铁表12.2,以鲜明每二个动作是怎么样影响Deleted和Inserted表的。

表12.2.Inserted和Deleted表的剧情

INSERT

DELETE

UPDATE

Inserted

插入列

修改前的列

Deleted

删除列

修改后的列

 

 

 

 

 

 

 

 

 

 使用 inserted 和 deleted 表

 (2009-03-12 12:44:13)

转载

标签: 

数据库,转载请注明出处:澳门新萄京:多少个表上的多少个触发器实践顺

上一篇:澳门新萄京针对Excel开发控件汇总,转换得到Da 下一篇:没有了
猜你喜欢
热门排行
精彩图文