更新时自动停用旧记录的触发器

发布于 2024-12-10 18:33:58 字数 388 浏览 0 评论 0原文

我正在刷新记录。我需要创建一个触发器来自动停用旧记录。记录被更新到一个不一样的新表中。新插入记录和原始记录之间的值由 2 列匹配，假设 col1 和 col2

ALTER TRIGGER TR_On_Renewed_Customer
ON CustomerTable2
FOR INSERT
AS
// psudeo code
// Deactive the old record in customertable1 
// if match is found between CustomerTable2 and 
// CustomerTable1 based on col1 and col2, then update Active ='No'

我有点迷失如何在此查询中使用 EXIST。

原文

I am renewing a record. I need to create a trigger that will automatically deactivate the old record. Records are renewed into a new table not the same. Values between the newly insert record and original record are match by 2 columns, let's say col1 and col2

ALTER TRIGGER TR_On_Renewed_Customer
ON CustomerTable2
FOR INSERT
AS
// psudeo code
// Deactive the old record in customertable1 
// if match is found between CustomerTable2 and 
// CustomerTable1 based on col1 and col2, then update Active ='No'

I am a little bit lost how to use EXIST in this query.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

断念 2024-12-17 18:33:58

关键是使用特殊的 Inserted 表，其中包含刚刚受到导致触发器触发的 INSERT 操作影响的行。

UPDATE c1
    SET Active = 'No'
    FROM Inserted i
        INNER JOIN CustomerTable1 c1
            ON i.col1 = c1.col1
                AND i.col2 = c1.col2

The key is to use the special Inserted table, which contains the rows that were just impacted by the INSERT operation that caused the trigger to fire.

UPDATE c1
    SET Active = 'No'
    FROM Inserted i
        INNER JOIN CustomerTable1 c1
            ON i.col1 = c1.col1
                AND i.col2 = c1.col2

回复收藏 0 原文

~没有更多了~