如果行更改除一列之外，则使用触发器将记录插入另一个表

Case

I have two tables with following structures (I removed 34 other columns that were not related to question)

我有两个表具有以下结构(我删除了34个与问题无关的其他列)

Table1
------
Id
LastAccessed
Data1
Data2

Table1_History
------
_Id
Action
Id
LastAccessed
Data1
Data2

Every time a user reads the record (with specific procedure) the LastAccessed timestamp will change.

每次用户读取记录(使用特定过程)时,LastAccessed时间戳都将更改。

I attached a trigger to Table1 which copies the record to history table if Data1 or Data2 (or in my case any other column changes); However I don't want a copy or record in case LastAccessed changes.

我将一个触发器附加到Table1,如果Data1或Data2(或者在我的情况下任何其他列发生更改),它会将记录复制到历史表中;但是,如果LastAccessed发生更改,我不想要副本或记录。

Here is my trigger

这是我的触发器

CREATE TRIGGER [TRIGGER!] 
   ON  TABLE1
   AFTER UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;
    IF NOT UPDATE([LastAccessed]) BEGIN
        IF EXISTS(SELECT * FROM inserted) BEGIN
            INSERT INTO [Table1_Hist](
                Action, Id, LastAccessed, Data1, Data2
            ) SELECT 
                'EDIT', Id, LastAccessed, Data1, Data2
            FROM deleted
        END ELSE BEGIN
            INSERT INTO [Table1_Hist](
                Action, Id, LastAccessed, Data1, Data2
            ) SELECT 
                'DELETE', Id, LastAccessed, Data1, Data2
            FROM deleted
        END
    END
END

This trigger will not copy row if LastAccessed and Data1 both change (which I want to). to solve this issue I can change the IF statement to

如果LastAccessed和Data1都发生了变化(我想要),则此触发器不会复制行。为了解决这个问题,我可以将IF语句更改为

IF UPDATE(Id) OR UPDATE(Data1) OR UPDATE(Data2) ... BEGIN

In this case it will work as intended

在这种情况下,它将按预期工作

Question: As I have 34 columns in my table it is not easy to specify every column in IF statement. Is there any easier way of doing this?

问题:由于我的表中有34列,因此在IF语句中指定每一列并不容易。有没有更简单的方法呢?

2 个解决方案

#1

Hopefully someone has a more elegant answer than this, but worst case you can generate that ugly IF statement for all 34 columns with a simple query:

希望有人有一个比这更优雅的答案,但最糟糕的情况是你可以通过一个简单的查询为所有34列生成丑陋的IF语句:

SELECT CASE WHEN column_id = 1 THEN 'IF ' ELSE 'OR ' END + 'UPDATE(' + name + ')' 
    FROM sys.columns 
    WHERE object_id = OBJECT_ID(N'[dbo].[Table1]') 
        AND name <> 'LastUpdated'
    ORDER BY column_id

Then you can just cut-and-paste the results for your IF statement... not an elegant solution, but it's quicker than typing it all by hand.

然后你可以只为你的IF语句剪切并粘贴结果...不是一个优雅的解决方案,但它比手动输入更快。

#2

CREATE TRIGGER [TRIGGER!] 
   ON  TABLE1
   AFTER UPDATE
AS 
BEGIN

INSERT INTO Table1_Hist (
Action
Id
LastAccessed
Data1
Data2
)
SELECT 'UPDATE',d.ID,d.LastAccessed,d.Data1,d.Data2, etc., etc.
FROM DELETED d
INNER JOIN INSERTED i ON d.id = i.id
WHERE 
COALESCE(d.data1,'') <> COALESCE(i.data1,'') OR
COALESCE(d.data2,'') <> COALESCE(i.data2,'') OR
<repeat for every attribute EXCEPT LastAccessed>
END

This will also work for inserts or updates of multiple rows, whereas your method would potentially insert rows where the data did not actually change.

这也适用于多行的插入或更新,而您的方法可能会插入数据实际上没有更改的行。

Also, you want to have separate UPDATE and DELETE triggers because your query should just dump the full row set from DELETED in case of an actual DELETE.

此外,您希望具有单独的UPDATE和DELETE触发器,因为在实际DELETE的情况下,您的查询应该只从DELETED转储完整的行集。

#1