时间戳

首页 > 解决方案 > 使用触发器维护日志以进行更新

sql-server - 使用触发器维护日志以进行更新

问题描述

我创建了以下数据库、表和触发器以进行更新以维护更新日志。当用户修改 name/salary/gender/departmentid 时，它会更新“tblEmployeeAudit”表中的日志。

现在，有一个问题，如果我们只是更新系统中已经更新的相同值，那么不应该在“tblEmployeeAudit”表中更新更新条目。

例如：- 如果 tblEmployee 的 EmployeeName Ravi 具有 Id=1 则如果更新相同的名称，则不应更新日志，但在此不会发生。请帮帮我。

Create database learning
use learning

USE [learning]
GO

/****** Object:  Table [dbo].[tblEmployee]    Script Date: 11/11/2019 3:54:13 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblEmployee](
    [Id] [int] NOT NULL,
    [Name] [nvarchar](30) NULL,
    [Salary] [int] NULL,
    [Gender] [nvarchar](10) NULL,
    [DepartmentId] [int] NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [learning]
GO

/****** Object:  Table [dbo].[tblEmployeeAudit]    Script Date: 11/11/2019 3:54:34 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblEmployeeAudit](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AuditData] [nvarchar](1000) NULL,
PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Alter trigger tr_tblEmployee_ForUpdate
on tblEmployee
For Update
As
Begin
    Declare @Id int
    Declare @OldName nvarchar(30),@NewName nvarchar(30)
    Declare @OldSalary int,@NewSalary int
    Declare @OldGender nvarchar(10),@NewGender nvarchar(10)
    Declare @OldDeptId int,@NewDeptId int

    Declare @AuditString nvarchar(1000)

    Select * into #TempTable
    from inserted

    While(Exists(Select Id from #TempTable))
    Begin
        Set @AuditString=''

        Select Top 1 @Id=Id,@NewName=Name,@NewSalary=Salary,@NewGender=Gender,@NewDeptId=DepartmentId from #TempTable

        Select @OldName=Name,@OldSalary=Salary,@OldGender=Gender,@OldDeptId=DepartmentId from deleted where Id=@Id

        Set @AuditString='Employee with Id='+CAST(@Id as nvarchar(4))+' changed '
        if(@OldName<>@NewName)
            Set @AuditString=@AuditString+'NAME from '+@OldName+' to '+@NewName

        if(@OldSalary<>@NewSalary)
            Set @AuditString=@AuditString+'Salary from '+@OldSalary+' to '+@NewSalary

        if(@OldGender<>@NewGender)
            Set @AuditString=@AuditString+'Gender from '+@OldGender+' to '+@NewGender

        if(@OldDeptId<>@NewDeptId)
            Set @AuditString=@AuditString+'DepartmentId from '+@OldDeptId+' to '+@NewDeptId

        insert into tblEmployeeAudit values(@AuditString)
        Delete from #TempTable where Id=@Id

    End

End



   update tblEmployee
    set Name='Joseph' where Id=4

注意：-即使在没有不匹配的行更新员工 Id=4 更改时，也关注插入的数据

标签： sql-servertsqldatabase-trigger

解决方案

不要创建变量来存储更改的值，而是使用插入和删除的表来检查值是否已更改，我认为这将解决您的问题

选择 * 从插入的我选择 * 从删除的 d

在 * 附近，您只能选择要检查其值的列名。

推荐阅读