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

问题描述：

我一直在寻找关于触发器的一些以前的答案在这里，但无法找到我需要的东西，但我确定我的问题已被问到/之前回答。创建一个触发器，当一列更新时将值插入到一个新表中

我试图跟踪table1中columnA和columnB的任何更改。

如果此值发生更改，我想通过将现有值和新值插入到具有日期的其他表中来跟踪值。

我一直在考虑使用像这样的插入，但不知道如何添加获取源表的现有的和新的值（表1）：

CREATE TRIGGER NewTrigger ON table1 
FOR INSERT 
AS 

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

go

我需要使用（我认为）

Before update ON table1 FOR EACH ROW 
    . 
    . 
    . 
BEGIN

并查看所有的变化，并插入这些第一次然后做更新后相同？

答

像这样的东西应该做你所需要的。您将在下面的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

如果你想你也可以从INSERTED和DELETED列添加到您的日志表，以及如果你想捕捉的是得到了插入或更新的实际列值。

的答案确定欢呼我会看看这个并回到你身边。 – Standage 2012-03-29 19:31:18

对不起，我确实想跟踪插入或更新的柱面中的实际值，这很容易完成？ – Standage 2012-03-29 19:40:34

我有一个基于你的答案工作的解决方案，所以谢谢！ – Standage 2012-03-29 21:05:38

答

这是所有的变化和所有列，但可以修改你怎么样：

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

我不擅长在sql中这么问问题。这个答案是从DB中存在的所有表中捕获的，还是我们必须给出一个特定的表名？ – SpringLearner 2015-04-03 07:57:31

这是一张桌子。您必须编写自己的动态脚本才能使触发器在多个/所有表上工作。 – jimdrang 2015-04-06 18:00:53

答

对ABS
，而不是更新CREATE TRIGGER trigge作为

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

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

相关推荐