2.对现有表,点动作完成就会触发烟花这个动作【

作者: www.9159.com  发布:2019-10-10

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引擎的触发器,使用它后,数据库服务器的性能会大大地降低(特别是在使用不当的时候,情况更糟),并且也不可以在每个表上做触发器,有时业务逻辑日志,触发器根本没用,另外触发器记录的信息有限,不足以提供分析、审计所要的信息。

 

日志模块架构体系: 用户表—》用户登录会话日志—》用户任务(模块)日志—》用户数据操作日志。

 www.9159.com 1

 

如果是按照每张业务单据表对应一个日志表,那么操作日志表最好不要放在同一个数据库上,可单独建立一个日志库,表结构就是对应的每张业务单据的表结构加上日志记录相关字段,日志表名则以业务单据表名+“_Logs”为统一后缀格式,以方便统计及批量处理日志表。

批量处理日志表,因为这 样相关的业务单据太多,不太可能每个业务单据都去手工建立一一对应的日志表,对于批量处理的事务,交给DBMS。主要是处理思路,数据库一般都支持处理数 据定义DML语句(创建表、视图等),在程序中动态调用处理定义日志表结构,然后将数据日志内容一起提交给数据库服务器就可以了,或者在数据库定义一个存 储过程处理。

   如何用SQL脚本复制创建表结构,笔者在此提供一个简单的SQL2000  SQL代码:复制表结构。

 

   注意:以上代码只是取表对像信息,如Image、二进制数据等等字段没有加入,因为这些数据没必加入日志。在插入日志数据内容时,同样也可以用上面的方式,提取需要的字段,插入日志表,并记录用户操作信息。

 

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

 www.9159.com 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语言。

  以后继续整理编程相关内容,希望大家多多关注。。。。

  

本文由9159.com发布于www.9159.com,转载请注明出处:2.对现有表,点动作完成就会触发烟花这个动作【

关键词:

上一篇:没有了
下一篇:没有了