SQL 创建一个触发器，在更新列时将值插入到新表中

Create a trigger that inserts values into a new table when a column is updated

提问by Standage

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.

我一直在查看有关此处触发器的一些先前答案，但找不到我确切需要的内容，但我确定我的问题之前已被问过/回答过。

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

我正在尝试跟踪 table1 中 columnA 和 columnB 的任何更改。

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.

如果此值发生变化，我想通过将现有值和新值插入带有日期的不同表来跟踪这些值。

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):

我一直在考虑对插入使用类似的东西，但不确定如何添加获取源表（table1）的现有值和新值：

CREATE TRIGGER NewTrigger ON table1
FOR INSERT
AS

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

go

I need to use (I think) the

我需要使用（我认为）

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?

并查看所有更改并先插入这些更改，然后在更新后执行相同操作？

回答by Darth Continent

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

像这样的事情应该做你需要的。您将在INSERT下面的语句中插入指示执行到的操作的值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

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

如果您想捕获插入或更新的实际列值，也可以从日志表中添加列INSERTED和DELETED向日志表中添加列。

回答by jimdrang

This is for all changes and all columns, but you can modify how you like:

这适用于所有更改和所有列，但您可以修改自己喜欢的方式：

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[trMyTrigger]
ON [dbo].[MyTable]
AFTER INSERT, UPDATE, DELETE
NOT FOR REPLICATION
AS
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with caller queries select statements.
    -- if an update/insert/delete occurs on the main table, the number of records affected
    -- should only be based on that table and not what records the triggers may/may not
    -- select.
SET NOCOUNT ON;

    -- Determine if this is an insert,update, or delete action

    DECLARE @action AS CHAR(1)
    DECLARE @count AS INT
    SET @action = 'I' -- SET action to 'I'NSERT by default.
    SELECT @count = count(*) FROM DELETED
    IF @count > 0
        BEGIN
            SET @action= 'D' -- SET action to 'D'ELETED.
            SELECT @count = count(*) FROM INSERTED
            IF @count > 0
                SET @action = 'U' -- SET action to 'U'PDATED.
        END

    IF @action = 'D'
        -- THIS IS A DELETE RECORD ACTION
        BEGIN
            INSERT INTO myBackupTable
        SELECT *,GETDATE() AS changeDate, 'DELETE' AS task FROM DELETED
        END
    ELSE
        BEGIN
            IF @action = 'I'
                 -- this is an INSERT record action
                BEGIN
                    INSERT INTO myBackupTable
                    SELECT *,GETDATE() AS changeDate, 'INSERT' as task FROM INSERTED
                END
             ELSE
                -- this is an UPDATE record action
                BEGIN
                    INSERT INTO myBackupTable
                    SELECT *,GETDATE() AS changeDate, 'UPDATE' as task  FROM INSERTED
                END
        END

回答by gowtham

create trigger trigge on abs
instead of update as

在 abs 上创建触发器触发器
而不是更新为

declare @idd int , @pricee money
  select @idd= ProductID from inserted 
  select @pricee = ListPrice from inserted 
  insert into prod values ( @idd , @pricee)
  print ' cannot change'

回答by user11130396

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