Capture changed rows in the SQL update trigger

存储架构 Hello, buddy! (源链)

How can catch the modified row(s) in update trigger to insert them into other table ?

In SQL Server we have only two virtual tables you can workwith inside a trigger, those are “INSERTED” and “DELETED”. For example:

Insert Operation:When you insert a new record “INSERTED” virtual table contains the newly inserted record, where as “DELETED” virtual remains empty.

Update Operation:When you update any record, first the old record will be placed into the “DELETED” virtual table and the newly updated record is hold by the “INSERTED” virtual table.

That means you can get the old value from “DELETED” and the currently updating value through “INSERTED” virtual table. you can query them like:

-- To get the old record value

SELECT * FROM DELETED

-- To get the updated value

SELECT * FROM INSERTED

Delete Operation:When you delete any particular record the deleted record will be inserted into the “DELETED” virtual table.

  1. SELECT * FROM UPDATED
    – gives ERROR.

  2. Try this:

    DECLARE @OldVal int, @NewVal int

    SELECT @OldVal = Col FROM DELETED

    SELECT @NewVal = Col FROM INSERTED

by holding the old and new values you can compare their state.

您可能感兴趣的

BeetlSQL 2.9.4 发布,Java Dao 工具 BeetlSQL 2.9.4 发布了,改进记录包括: #IEDB8 输入参数不支持数组的bug修复 #IEEF9 增加templatePage方法 #IEEFF PageQuery 设置混合参数bug修复 com.ibeetl beetlsql 2.9...
SQL Query Method Generated by Rails #find There is an Article model with id, title, created_at, and updated_at columns in the database and no additional logic in the model. In the Rails conso...
Building SQL ConstantCare®: Why People Aren’t Usin... When Microsoft first talked about Query Store , I was wildly excited. It’s basically a black box flight data recorder for SQL Server, tracking ...
Why do the values ​&ZeroWid... I have to write a query to display the studentid and the total fees paid by each student and then sort the result based on studentid in ascending or...
SQL in 30 seconds SQL (generally pronounced "sequel") stands for Structured Query Language.It's a language that gives you the ability to manage relational databas...
Hello, buddy!责编内容来自:Hello, buddy! (源链) | 更多关于

阅读提示:酷辣虫无法对本内容的真实性提供任何保证,请自行验证并承担相关的风险与后果!
本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » Capture changed rows in the SQL update trigger



专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录