Update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Trigger

Author

Topic

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-21 : 00:18:20

Hi,

Can anyone tell me is it possible to take the updated row using update trriger,

Regards,
Thameem.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 00:19:45

yes. it is in the inserted table

rauof_thameem
Starting Member

31 Posts

Posted - 2007-06-21 : 00:23:14

a row is already is in table, i have upadted a particular column, i want the particular row whose column has been updated. in Inserted table we will have only the new inserted value na..,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-21 : 00:28:24

for INSERT operation, the inserted contains newly inserted rows
for UPDATE operation, the deleted contains rows before update and inserted contains rows after update
from BOL

quote:

Using the inserted and deleted Tables
Two special tables are used in trigger statements: the deleted table and the inserted table. Microsoft® SQL Server™ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.

The inserted and deleted tables are used primarily in triggers to:

Extend referential integrity between tables.

Insert or update data in base tables underlying a view.

Check for errors and take action based on the error.

Find the difference between the state of a table before and after a data modification and take action(s) based on that difference.
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

When you set trigger conditions, use the inserted and deleted tables appropriately for the action that fired the trigger. Although referencing the deleted table while testing an INSERT, or the inserted table while testing a DELETE does not cause any errors, these trigger test tables do not contain any rows in these cases.