创建_Log表及触发器,ERP设计之系统基础管理

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人士来实现。

初藳地址:8、ERP设计之系统基础管理(BS)-日志模块设计作者:ShareERP

  可是一些时候有的简便的专业要求大家协和去做到,不可能每回都去麻烦DB职员,所以说,编制程序人士要全才,除了编制程序认为的专门的工作知识也要读书,举例js,css,html,t-sql等部分语法,不自然要去明白,但是要熟稔,最少语法可以看懂,这样对大家的编制程序有经济的成效,出现相当错误,我们也利于调节和测量试验,以便最快找寻荒谬。

 

日记模块基本要素包涵:

客商会话、登陆、注销、模块加载/卸载、数据操作(增/删/改/审/弃/关等等)、数据恢复生机、日志查询,假设高供给的顾客大概还亟需审计深入分析、计算报告。

         如果想加强顾客体验,能够从客户日志分析中吸收越多客商操作行为上的数额,以便我们改进程序模块,加深客户体验。

 

布置日志模块,要思虑多少个规模:

1、  客商会话管理:主要记录报到、注销、顾客端新闻。

2、  顾客作为管理:首要记录客商操作行为习贯,记录模块加载/卸载、功效使用率。

3、  数据操作日志管理:首要记录客户数据流的更动景况,可追溯、剖判、苏醒。

4、  日志深入分析审计:管理解析日志,总计与报告。

 

对话/任务日志相比轻便,着重在数额操作日志,因为ERP系统数据表多,结构复杂,数据量也大。数据操作先要理清记录日志的诀要。扩展记录是否要记日志,笔者的接头没有须求。独有当数码被改换或删除时才必要记录日志。倘若扩充内需记录,那么它的数据量就非凡大了,不推荐。修改数据时或删除时,记录修改或删除前的多寡,相同的时候记录客商会话音信,实际不是客户ID,那样防范不法客户篡改日志数据。

 

数据日志表如何布置,是 用二个表,依然各类表对应三个日志表,小编推荐前面一个。要是手艺达不到的话,就用一个表,但追溯、恢复生机、分析数据就难了,因为表结构不一,不能够冗余的将日志 数据放在一齐,不平价恢复生机,解析。同有的时候间,要是分裂的表对应二个日志表管理起来也是特别复杂,增添编制程序难度,可是技能是足以摆平的。

 

留意:刚烈不推荐使用DBMS引擎的触发器,使用它后,数据库服务器的习性会大大地下跌(非常是在使用不当的时候,情形更糟),何况也不得以在每一种表上做触发器,有时职业逻辑日志,触发器根本没用,另外触发器记录的消息有限,不足以提供深入分析、审计所要的音信。

 

日志模块架构种类: 顾客表—》客户登入会话日志—》顾客职责(模块)日志—》客户数据操作日志。

 图片 1

 

借使是依据每张业务单据表对应三个日志表,那么操作日志表最佳不要放在同八个数据库上,可独自创建一个日志库,表结构便是应和的每张业务单据的表结构丰盛日志记录相关字段,日志表名则以工作单据表名 “_Logs”为统一后缀格式,以有益总括及批量管理日志表。

批量管理日志表,因为那样相关的工作单据太多,不太大概每个职业单据都去手工业建构梯次对应的日志表,对于批量甩卖的事务,交给DBMS。重假使拍卖思路,数据库经常都支持处理数 据定义DML语句(制造表、视图等),在程序中动态调用管理定义日志表结构,然后将数据日志内容一齐付出给数据库服务器就足以了,可能在数据库定义八个存储进程管理。

   如何用SQL脚本复制创制表结构,我在这里提供贰个大约的SQL两千  SQL代码:复制表结构。

 

   注意:以上代码只是取表对像音信,如Image、二进制数据等等字段未有参加,因为那几个多少没必参与日志。在插入日志数据内容时,同样也足以用地点的章程,提取需求的字段,插入日志表,并记下客户操作新闻。

 

 下图为作者的日志浏览分界面:

 图片 2

 

 

下篇:系统基础管理(BS)- 报表框架设计  待续……

来源:分享ERP  http://www.shareerp.com 联系QQ:908916944

  言归正传,什么事触发器,看名就可见意思,正是你做一个操作,就能够触发另三个事变,去试行一些操作。

  举例您点烟花,点是二个动作,烟花是另八个动作,点动作一鼓作气就能触发烟花那个动作。

  还或者有就是触发器必须依靠三个基点,比如依附于某一张表,就如编制程序中事件那么些概念。

  上面我们通过多个大约的实例,和豪门一步一步的来明白和利用触发器。

  实例须要:

    1,建商品表(Store),订单表(orders),日志表(Logs)

    2,创造订单表插入触发器,完毕插入一条订单音信,商品表中货色数量相应回退,订单中的总金额相应增添。

    3,成立订单表更新触发器,达成立异一条订单新闻,商品表中货品数量相应改造,订单中的总金额相应更改。(和2类似)

    4,创设日志表触发器,实现创新商品表价格变化景况。

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)

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之后的多寡。语法便是如此简单。

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语言。

  今后继续整治编程相关内容,希望大家多多关怀。。。。

  

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:创建_Log表及触发器,ERP设计之系统基础管理

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