SQL UPDATE triggers to get information about the rows being update

Source: Internet
Author: User
Tags copy insert sql
Copy Code code as follows:
Create Trigger Tgname
On TB
For update
As
If Update (recommend)
Begin
Update TB Set commenddate= (GETDATE ()) from TB inner join inserted on Tb.vlistid=inserted.vlistid
End

Recommend represents the field being updated.
The key is the inserted table
Two special tables are used in trigger statements: Deleted tables and inserted tables.
The Deleted table is used to store a copy of the rows affected by the DELETE and UPDATE statements. When the delete or UPDATE statement is executed, the row is removed from the trigger table and transferred to the deleted table. Deleted tables and trigger tables usually do not have the same rows.

The Inserted table is used to store a copy of the rows affected by the INSERT and UPDATE statements. In one insert or update transaction, new rows are added to both the inserted table and the trigger table. The row in the Inserted table is a copy of the new row in the trigger table.

1. Inserting operation (insert)
Inserted table has data, deleted table has no data

2. Delete operation (delete)
Inserted table has no data, deleted table has data

3. Update operation (updated)
Inserted table has data (new data), deleted table has data (old data)

Why do you sleep before you die?

Related Article

Alibaba Cloud 10 Year Anniversary

With You, We are Shaping a Digital World, 2009-2019

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.