如何在更新时使用触发器执行日志？ [英] How to do a log using a trigger on update?

问题描述

嘿伙计们，我在T-SQL上非常糟糕/新手，但我需要它来处理我正在进行的项目。



我想要什么这里做的是能够在更新时从其他表（事件）填充日期表（日志）（基本上是一个日志）。



我有这个伪代码：

Hey guys, I'm really bad/new at T-SQL but I need it for a project I'm working on.

What I want to do here is to be able to populate a table (logs) with date from another table (events) when this is updated (essentially making a log).

I have this pseudocode:

CREATE TRIGGER [dbo].[EventUpdate]
   ON  [dbo].[Events]
   AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    WHILE tablesupdated.hasNext()
    BEGIN
        [dbo].[Logs].[date] = dateTime.Now();
        [dbo].[Logs].[user] = "John";
        [dbo].[Logs].[changes] = [dbo].[Events].[updatedTable].previousval() + "-->" + [dbo].[Events].[updatedTable].afterval();
        [dbo].[Logs].[eventId] = [dbo].[Events].[id];
    END
END
GO

更多的是让你知道我想要什么以及我在这方面有多糟糕......你能帮忙吗？



PS：我完全接受如何完成这项工作的新建议。

It's more to give you an idea of what I want and how bad I am at this... can you guys help please?

PS: I'm completely open to new suggestion on how to get this done.

推荐答案

CREATE TRIGGER [dbo].[EventUpdate]
   ON  [dbo].[Events]
   AFTER UPDATE
AS
BEGIN
       SET NOCOUNT ON;

       INSERT INTO [dbo].Logs([date],[user],[changes],[eventId])
             SELECT getdate(), 'john', I.columnname + '-->' + D.columnname, I.id
             FROM Inserted I
                  INNER JOIN Deleted D ON I.id = D.id

END
GO

在触发器中有一对名为inserted和deleted的概念表，其中包含记录正在更新（新的和以前的数据）。 （参见创建触发器 [ ^ ]文档 - 它包含一个审计示例）。



您需要遍历这对表中的行并将它们记录到表中。

Within a trigger there are a pair of conceptual tables called "inserted" and "deleted" which contains the records that are being updated (new and previous data). (See CREATE TRIGGER[^] documentation - it has an audit example included).

You need to iterate through the rows in this pair of tables and log them to the table.

这篇关于如何在更新时使用触发器执行日志？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！