创建一个触发器，该触发器在列更新时将值插入到新表中 [英] Create a trigger that inserts values into a new table when a column is updated

问题描述

我一直在这里查看有关触发器的先前答案，但找不到我确切需要的答案，但是我确定我的问题之前已经被询问过。

I've been looking at some previous answers on triggers on here but can't find what I need exactly but I'm sure my question has been asked/answered before.

我试图跟踪对表1中columnA和columnB的任何更改。

I'm trying to keep track of any changes to columnA and columnB in table1.

如果此值更改，我想通过插入来跟踪值

If this value changes I want to keep track of the values by inserting the existing value and the new Value into a different table with a date.

我一直在考虑使用类似的方法进行插入，但是不确定如何添加get源表（表1）的现有值和新值：

I've been looking at using something like this for the insert but not sure how to add get the existing and new values of the source table (table1):

CREATE TRIGGER NewTrigger ON table1
FOR INSERT
AS

INSERT INTO table2
        (columnA , columnB, todaysDate)
    .
    .

go

我需要使用

Before update ON table1 FOR EACH ROW
   .
   .
   .
BEGIN

并仔细查看所有更改，然后先插入这些更改，然后在

and look through all the changes and insert these first then do the same after the Update?

推荐答案

类似的东西应该可以满足您的需求。您将在下面的 INSERT 语句中插入值，该值指示在 MyLogTable 中执行的操作。

Something like this should do what you need. You would have the INSERT statements below insert values indicating the operation performed into MyLogTable.

CREATE TRIGGER [dbo].[TRIG_MyTable]
ON [dbo].[MyTable]
AFTER INSERT, UPDATE

AS 

DECLARE @INS int, @DEL int

SELECT @INS = COUNT(*) FROM INSERTED
SELECT @DEL = COUNT(*) FROM DELETED

IF @INS > 0 AND @DEL > 0 
BEGIN

    -- a record got updated, so log accordingly.

    INSERT INTO MyLogTable
    SELECT 'New Values', getdate() FROM INSERTED

    INSERT INTO MyLogTable
    SELECT 'Old Values', getdate() FROM DELETED

END

ELSE 
BEGIN

    -- a new record was inserted.

    INSERT INTO MyLogTable
    SELECT 'Insert', getdate() FROM INSERTED

END

如果需要，还可以添加 INSERTED 和 DELETED

If you wanted to you could also add columns from INSERTED and DELETED to your log table as well if you wanted to capture the actual column values that got inserted or updated.

这篇关于创建一个触发器，该触发器在列更新时将值插入到新表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！