触发器的操作一般是DB人员来完成,2.对现有表

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的行事表中,就疑似AFTEWrangler触发器同样。相符地,在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

钦定第1个和尾声一个触发器  
  可将与表相关联的   AFTEPRADO   触发器之一钦定为各类   INSERT、DELETE   和   UPDATE   触发动作推行的第多个或最终叁个   AFTE科雷傲   触发器。在第八个和最后三个触发器之间激发的   AFTEOdyssey   触发器将按未定义的依次履行。  
   
  若要内定   AFTELacrosse   触发器的逐生龙活虎,请使用   sp_settriggerorder   存款和储蓄进度。可用的选项有:    
   
  第一个    
  钦点该触发器是为触发操作激发的第四个   AFTEEnclave   触发器。  
   
  最后贰个    
  内定该触发器是为触发操作激发的末尾贰个   AFTEENCORE   触发器。  
   
  无    
  钦定触发器的慰勉未有特定的逐大器晚成。主要用于重新安装第二个或最后叁个触发器。  
   
  以下是采用   sp_settriggerorder   的示例:  
   
  sp_settriggerorder   @triggername   =   'MyTrigger',   @order   =   'first',   @stmttype   =   'UPDATE'  
   
   
   
  主要     第二个和尾声八个触发器必得是四个不等的触发器。  
   
   
  只怕还要在表上定义了   INSERT、UPDATE   和   DELETE   触发器。各类语句类型或许都有友好的率先个和最终三个触发器,但它们不能够是同风流罗曼蒂克的触发器。  
   
  即使为有些表定义的第三个或最终叁个触发器不富含触发操作,如   FO奥德赛   UPDATE、FO哈弗   DELETE   或   FOLacrosse   INSERT,则缺乏的操作将从未第三个或最终多个触发器。  
   
  无法将   INSTEAD   OF   触发器内定为率先个或最后三个触发器。在对根基表进行立异前激发   INSTEAD   OF   触发器。不过,假使由   INSTEAD   OF   触发器对根基表展开翻新,则这个校正将爆发于在表上定义触发器(满含率先个触发器卡塔尔之后。举例,要是视图上的   INSTEAD   OF   触发器更新基表而且该基表包蕴四个触发器,则该四个触发器在   INSTEAD   OF   触发器插入数据在此之前激发。有关越来越多音讯,请参见钦点触发器何时激发。  
   
  若是   ALTE揽胜极光   TCRUISERIGGEEvoque   语句改革了第一个或倒数触发器,则将除了   First   或   Last   天性,况且顺序值将安装为   None;必得用   sp_settriggerorder   重新恢复设置此顺序。  
   
  OBJECTPROPERTY   函数使用性质   ExecIsFirstTrigger   和   ExecIsLastTrigger   报告触发器的相继是率先个还是最终叁个。  
   
  复制将为自家是即时更新订户或排队更新订户的任何表自动生成第七个触发器。复制供给它的触发器是首先个触发器。假使尝试使具备率先个触发器的表变为当时更新订户或排队更新订户,复制将掀起错误。假使使表变为当下更新订户或排队更新订户之后使客商定义触发器成为第三个触发器,则   sp_settriggerorder   会重临多少个不当。如若在复制触发器上选取   ALTE凯雷德,或利用   sp_settriggerorder   将复制触发器校勘为结尾触发器或无触发器,则订阅将不可能科学职业。  

  譬喻你点烟花,点是三个动作,烟花是另一个动作,点动作完结就能接触烟花那一个动作。

AS

多少个触发器完全相像吗,借使全部相仿,会先履行后确立的触发器。推行的各种与建设构造的种种相反。

  还或然有正是触发器必得依据三个入眼,比方依附于某一张表,就如编制程序中事件这么些定义。

INSERT weblog (activity) SELECT user_name FROM Deleted

  下边大家通过二个简短的实例,和大家一步一步的来精通和应用触发器。

该触发器自动地创建二个webusers表的记录。当在webusers表内去除一个顾客的人名时,触发器会自动地把该姓名插入到weblog表中。假设你一不当心实行了上边包车型客车言语:

  实例须求:

DELETE webusers

    1,建筑商品表(Store卡塔尔国,订单表(orders卡塔尔国,日志表(Logs卡塔尔国

该语句会删除在webusers表内的保有记录。常常地,这几个记录社长久地错失,但是上面包车型客车触发器会在有记录从webusers表中剔除时自动的触及。该触发器会检讨表Deleted来分明有这个在webusers表内的笔录被剔除,並且把具备删除了的记录拷贝到weblog表中。

    2,创制订单表插入触发器,完结插入一条订单新闻,商品表中货品数量相应核减,订单中的总金额相应增添。

为了还原那多少个意外被删去的记录,你能够使用INSERT和SELECT语句再贰遍把它们从weblog表中拷贝到webusers表中。倘让你不能够容许意外市错过一条记下时,你能够采取方面的点子来创建叁个表内数据的备份。

本文由必威发布于必威-数据,转载请注明出处:触发器的操作一般是DB人员来完成,2.对现有表

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。