quote:

---

Originally posted by rmiao

Query inserted and deleted tables in the trigger.

---

quote:

---

The record is already on the table. The moment a certain application will do an update on a certain row, I want to retrieved that row and check if the column ProcessBy is not equal to zero for an instance. Inserted and deleted will not give me what I want to.

---

quote:

---

Originally posted by khtan

quote:

---

The record is already on the table. The moment a certain application will do an update on a certain row, I want to retrieved that row and check if the column ProcessBy is not equal to zero for an instance. Inserted and deleted will not give me what I want to.

---

Inserted and deleted table will contain the before and after changes of the row. Why it is not giving the info you want ?

---

KH
[spoiler]Time is always against us[/spoiler]

---

quote:

---

Sorry I will clarify want I want. Want I want is during the UPDATE statement of the client, I want to identify the row/s affected during the UPDATE statement. Inserted is fired during Insert and Deleted is fired during Delete right?

---

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.

---

quote:

---

Originally posted by rmiao

Update is delete then insert, that's why you can get what updated in inserted and deleted tables.

---