我想将表Item中的所有字段更改记录到名为事件的日志表中。
CREATE TABLE [dbo].[Items]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](100) NULL,
[Description] [nvarchar](max) NULL,
[ParentId] [int] NULL,
[EntityStatusId] [int] NOT NULL,
[ItemTypeId] [int] NOT NULL,
[StartDate] [datetimeoffset](7) NULL,
[DueDate] [datetimeoffset](7) NULL,
[Budget] [decimal](18, 2) NULL,
[Cost] [decimal](18, 2) NULL,
[Progress] [int] NULL,
[StatusTypeId] [int] NULL,
[ImportanceTypeId] [int] NULL,
[PriorityTypeId] [int] NULL,
[CreatedDate] [datetimeoffset](7) NULL,
[HideChildren] [bit] NOT NULL,
[TenantId] [int] NOT NULL,
[OwnedBy] [int] NOT NULL,
[Details] [nvarchar](max) NULL,
[Inserted] [datetimeoffset](0) NOT NULL,
[Updated] [datetimeoffset](0) NOT NULL,
[InsertedBy] [int] NULL,
[UpdatedBy] [int] NULL,
)
对于每个更改的列，我想在此表中添加一行。该表将保留对Item表的更改，但以后还将保留对其他表的更改。我希望触发器尽可能动态，因此相同的基本触发器也可以用于其他表。如果将列添加/删除到表中，则SP应该发现它并且不会中断。
CREATE TABLE [dbo].[Events]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[RecordId] [int] NOT NULL, -- Item.Id
[EventTypeId] [int] NOT NULL, -- Always 2
[EventDate] [datetimeoffset](0) NOT NULL, --GetUTCDate()
[ColumnName] [nvarchar](50) NULL, --The column name that changed
[OriginalValue] [nvarchar](max) NULL, --The original Value
[NewValue] [nvarchar](max) NULL, --The New Value
[TenantId] [int] NOT NULL, --Item.TentantId
[AppUserId] [int] NOT NULL, --Item.ModifiedBy
[TableName] [int] NOT NULL --The Name of the Table (Item in this case, but later there will be others)
)
我正在尝试编写一个更新触发器，但是发现它很困难。
我知道这里有插入和删除表，其中包含新值和旧值。
那么我该如何实现呢？似乎应该是动态的，这样，如果添加了列，则不会破坏任何内容。
如果我使用C＃编写此代码，则将获取所有列名并遍历它们，找到已更改的字段，然后为每个字段创建一个事件。但是我看不到如何用SQL做到这一点。
更新以回答：
在SSMS中编辑时，此答案有效。但是，在实践中，该应用程序使用EntityFramework，并且似乎在做一些奇怪的事情，因为这是要记录的内容。请注意，在原始/新建中实际上只有一列具有不同的值。因此，我尝试在执行插入操作之前检查值是否确实不同。
+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
| Id | RecordId | EventTypeId | EventDate | ColumnName | OriginalValue | NewValue | TenantId | AppUserId | TableId | TableName |
+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
| 21 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Name | Task 2 | Task 2A | 8 | 11 | NULL | Item |
| 22 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Description | NULL | NULL | 8 | 11 | NULL | Item |
| 23 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | ParentId | 238 | 238 | 8 | 11 | NULL | Item |
| 24 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | EntityStatusId | 1 | 1 | 8 | 11 | NULL | Item |
| 25 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | ItemTypeId | 3 | 3 | 8 | 11 | NULL | Item |
| 26 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | StartDate | NULL | NULL | 8 | 11 | NULL | Item |
| 27 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | DueDate | NULL | NULL | 8 | 11 | NULL | Item |
| 28 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Budget | NULL | NULL | 8 | 11 | NULL | Item |
| 29 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Cost | NULL | NULL | 8 | 11 | NULL | Item |
| 30 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Progress | NULL | NULL | 8 | 11 | NULL | Item |
| 31 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | StatusTypeId | 1 | 1 | 8 | 11 | NULL | Item |
| 32 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | ImportanceTypeId | NULL | NULL | 8 | 11 | NULL | Item |
| 33 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | PriorityTypeId | NULL | NULL | 8 | 11 | NULL | Item |
| 34 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | OwnedBy | 11 | 11 | 8 | 11 | NULL | Item |
| 35 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Details | <p><span></span></p> | <p><span></span></p> | 8 | 11 | NULL | Item |
| 36 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Inserted | 0001-01-01 00:00:00 +00:00 | 0001-01-01 00:00:00 +00:00 | 8 | 11 | NULL | Item |
| 37 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | Updated | 0001-01-01 00:00:00 +00:00 | 0001-01-01 00:00:00 +00:00 | 8 | 11 | NULL | Item |
| 38 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | InsertedBy | 11 | 11 | 8 | 11 | NULL | Item |
| 39 | 397 | 2 | 2018-04-22 15:42:16 +00:00 | UpdatedBy | 11 | 11 | 8 | 11 | NULL | Item |
+----+----------+-------------+----------------------------+------------------+----------------------------+----------------------------+----------+-----------+---------+-----------+
这是使用COLUMNS_UPDATED的一种方法。触发器不依赖于列名，因此您可以毫无问题地添加或删除列。我在查询中添加了一些评论
create trigger audit on Items
after update
as
begin
set nocount on;
create table #updatedCols (Id int identity(1, 1), updateCol nvarchar(200))
--find all columns that were updated and write them to temp table
insert into #updatedCols (updateCol)
select
column_name
from
information_schema.columns
where
table_name = 'Items'
and convert(varbinary, reverse(columns_updated())) & power(convert(bigint, 2), ordinal_position - 1) > 0
--temp tables are used because inserted and deleted tables are not available in dynamic SQL
select * into #tempInserted from inserted
select * into #tempDeleted from deleted
declare @cnt int = 1
declare @rowCnt int
declare @columnName varchar(1000)
declare @sql nvarchar(4000)
select @rowCnt = count(*) from #updatedCols
--execute insert statement for each updated column
while @cnt <= @rowCnt
begin
select @columnName = updateCol from #updatedCols where id = @cnt
set @sql = N'
insert into [Events] ([RecordId], [EventTypeId], [EventDate], [ColumnName], [OriginalValue], [NewValue], [TenantId], [AppUserId], [TableName])
select
i.Id, 2, GetUTCDate(), ''' + @columnName + ''', d.' + @columnName + ', i.' + @columnName +', i.TenantId, i.UpdatedBy, ''Item''
from
#tempInserted i
join #tempDeleted d on i.Id = d.Id and isnull(Cast(i.' + @columnName + ' as varchar), '''') <> isnull(Cast(d.' +@columnName + ' as varchar), '''')
'
exec sp_executesql @sql
set @cnt = @cnt + 1
end
end
我已将表的TableName列的数据类型更改Events为nvarchar。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句