时间戳

首页 > 解决方案 > 不使用插入或删除表的审计日志

sql - 不使用插入或删除表的审计日志

问题描述

我需要设置审核日志。我需要一种方法来做到这一点，而不使用删除和插入的表。我正在使用 SQL Server Management Studio 2012。

表 A（第一条记录）。Insurer 和 InsuranceResponsible 是人员的代码。

Id YearReview    Insurer   InsuranceResponsible    TicketNr  InsReviewId  ReviewDate Comment IdInsurance

124   2017        1496         2345                TK201705     1        2017-09-29    Test     234
119   2017        1567         4567                TK201608     2        2017-09-29    Axa      1024
45    2016        1567         4567                TK201608     1        2016-05-30    All      1024

表 A（几个月后）** 注意：yearReview 和 IdInsurance - 一年内永远不会有两个相同的 IdInsurance。因此，如果有人对特定 IdInsurance 的保险进行审查，它将删除最后一条记录，但始终会记录更改。要记录的有：@Insurer、@InsuranceResponsible、@TicketNr、@InsReviewId、@comment。

Id YearReview    Insurer   InsuranceResponsible    TicketNr  InsReviewId  ReviewDate Comment IdInsurance

521   2018       2456         1459                TK201805     1        2018-09-29    Ti       1274
520   2017       1496         2345                TK201608     3        2018-09-29    Remove   1024     
124   2017       1496         2345                TK201705     1        2017-09-29    Test     234
119   2017       1567         4567                TK201608     2        2017-09-29    Axa      1024
45    2016       1567         4567                TK201608     1        2016-05-30    All      1024

表 B - 审计日志表 - 表的标题是由另一个人制作的（我认为 oldvalue 和 newvalue 在日志表中不好......但现在我必须坚持这些）。我没有在这里包括 LastChangeUser 也没有 LastChangeDate 但它们确实存在。

    Id    AlteredField  OldValue  NewValue  IdInsurance  CreationDate CreationUser Event

    34        n/a          n/a      n/a      1024         2016-05-30      1567   'New Review 2016'
    45      'InsReview'     1       2        1024         2017-09-29      1567   'Review 2017'
    46  
    (...)

    1000      n/a          n/a      n/a      1274         2018-09-29      2456   'NewReview 2018'  
    1001    'InsReview'     2       3        1024         2018-09-29     1486  'Review 2018'

一种可能性，我不确定是对的……

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UpdateTableLog] 
     @IdInsurance int
    ,@YearReview char(4)
    ,@Insurer int
    ,@InsuranceResponsible int
    ,@TicketNr nvarchar(50)
    ,@InsReviewId int
    ,@comment nvarchar(255)

AS
BEGIN
    SET NOCOUNT ON;


    DECLARE @OldInsurer int
    DECLARE @OldInsuranceResponsible int
    DECLARE @OldTicketNr nvarchar(50)
    DECLARE @OldInsReviewId int
    DECLARE @OldComment nvarchar(255)


    -- SELECT OLD VALUES IN TABLE A 
    SET @OldInsurer = (SELECT TOP 1 [Insurer] from tableA WHERE Id = @IdInsurance and YearReview=@YearReview )
    SET @OldInsuranceResponsible = (SELECT TOP 1 [InsuranceResponsible] from tableA WHERE Id = @IdInsurance and YearReview=@YearReview )
    SET @OldTicketNr = (SELECT TOP 1 [TicketNr] from tableA WHERE Id = @IdInsurance and YearReview=@YearReview )
    SET @OldInsReviewId = (SELECT TOP 1 [InsReviewId] from tableA WHERE Id = @IdInsurance and YearReview=@YearReview  ) 
    SET @OldComment = (SELECT TOP 1 [Comment] from tableA WHERE Id = @IdInsurance and YearReview=@YearReview )
    SET @OldIdInsurance = (SELECT TOP 1 [IdInsurance] from tableA WHERE Id = @IdInsurance and YearReview=@YearReview )


    -- INSURER: EVALUATE IF SOMETHING HAS CHANGED
        IF @OldInsurer is not NULL

        BEGIN
        IF @OldInsReviewId <> @InsReviewId
                BEGIN 
                          INSERT INTO TableB ('InsReview',@OldInsReviewId,@InsReviewId,@IdInsurance,getdate(),@Insurer, concat('Review ' + year(getdate()) )
                    END

    same reasoning for the remaining 4.
        END

        ELSE   -- FOR the case where there are no lines in table B to compare, meaning a new review..

            BEGIN 
                  INSERT INTO TableB ('N/A','n/a','n/a',@IdInsurance,getdate(),@Insurer, concat('New Review ' + year(getdate()) )
            END
       END

所以我的问题是：

1）您是否同意为获取上面显示的表B而设计的存储过程？

2）是否有可能实现同一个表B避免声明变量而不使用SQL Server 2012的插入/删除表？也许不吧。

标签： sqlsql-server-2012

解决方案

对于这个审计表模式，我必须推荐使用deletedand insertedwith 。UNPIVOT问题是如何在没有他们的情况下做到这一点......

问题中的存储过程将执行您尝试执行的操作。可以改进读取旧值的部分。所有查询的FROMandWHERE子句都是相同的，因为它们正在读取同一行，因此应该有一个查询。在旧版本中，表会被重复读取，这对性能不利，并且会增加数据库的负载，从而降低其他所有操作的速度。小表并不明显，但是有几亿行这种事情会产生很大的不同。而且，由于我们已经确定 Id 和 YearReview 是唯一键，因此TOP不需要操作符。

所以SELECT OLD VALUES IN TABLE A会更好：

SELECT 
  @OldInsurer = [Insurer]
, @OldInsuranceResponsible = [InsuranceResponsible]
, @OldTicketNr = [TicketNr]
, @OldInsReviewId = [InsReviewId]
, @OldComment = [Comment]
, @OldIdInsurance = [IdInsurance]
FROM tableA 
WHERE Id = @IdInsurance and YearReview=@YearReview

推荐阅读