SQL触发器将更新更改复制到另一个表 [英] SQL Triggers to duplicate update changes to another table

问题描述

所以我有一个间歇性问题，需要临时解决.

So I have an intermitant problem that I need a temporary fix for.

(每两周(3-4周)间歇性地导致两张桌子出现问题)

(something is causing issues with two tables intermittently (every 3-4 weeks))

一个表被完全清除，另一个表似乎已删除单个记录...

One table gets completly cleared out and another seems to have individual records removed...

由于这是一个持续存在的问题，我们仍在调查主要原因，但需要临时修复.

as this is an ongoing issue we are still investigating the main cause but need a temporary fix.

这样，我为每个表设置了第二个备份"表，并尝试设置触发器以复制所有插入"和更新"命令

As such I have setup a second "Backup" table for each of these tables and am trying to setup triggers to copy ALL Insert and Update commands

我已经毫无问题地创建了插入触发器.

I have created the Insert triggers with no problem.

ALTER Trigger [dbo].[MainInsertBackup] 
On [dbo].[tblMain] AFTER INSERT AS 
BEGIN 
    INSERT INTO tblMainBackup 
    SELECT * FROM inserted 
END

现在我需要一个对更新执行相同操作的触发器(在tblMainBackup上执行更新).

now I need a trigger that does the same for updates (performs the updates on the tblMainBackup).

这样，我应该确保备份表具有来自主表的所有信息，但是如果以某种方式删除了一条记录，则可以将其从备份表复制回主表.

this way I should ensure that the backup table has all of the information from the main but if a record is removed somehow it can be copied back to the main table from the backup table.

只有我发现的示例在更新的各个字段中执行更新，或将更新中的一条新记录插入日志表中.

Only examples I have found perform updates in individual fields on the update, or insert a whole new record on the update into a log table.

任何帮助都可以得到.

推荐答案

您可以尝试使用此UPDATE触发器来更新tblMainbackup中的行，同时更新tblMain表中的行

You can try to use this UPDATE trigger to update the rows in tblMainbackup while update row in tblMain table

CREATE Trigger [dbo].[MainUpdateBackup] 
On [dbo].[tblMain] AFTER UPDATE AS 
BEGIN     
    UPDATE tblMainbackup
    SET tblMainbackup.col1 = i1.col1,
        tblMainbackup.col2 = i1.col2,
        tblMainbackup.col3 = i1.col3    
    FROM tblMainbackup t1
          INNER JOIN inserted i1
          ON t1.Id = i1.ID
END

这篇关于SQL触发器将更新更改复制到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！