SQL Server Performance Forum – Threads Archive

Trigger

I am using the folling trigger where it updates lastmodified column to current date when there is an update on the table but wht i need is, i just want to update only those records where there is a an update but not complete table. pls help. CREATE TRIGGER Q_U on dbo.Quarter
FOR UPDATE
AS BEGIN UPDATE Quarter
SET LastModified = getdate() END Thanks!
"He laughs best who laughs last"

Use the inserted and deleted virtual tables: within a trigger, they give you a snapshot of all affected rows before the action (deleted) and after the action (inserted). You simply join the table to a virtual table on the PK column(s), in a FROM clause. CREATE TRIGGER Q_U on dbo.Quarter FOR UPDATE AS BEGIN UPDATE Quarter SET LastModified = getdate() FROM Quarter INNER JOIN inserted ON Quarter.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />k_column> = inserted.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />k_column> END

do u mean i need to create 2 virtual tables(inserted& deleted) with same columns? Thanks!
"He laughs best who laughs last"

No, think of these "tables" as snapshots of the log file before and after the modification. You do not need to care about their creation. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

From BOL
deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action Madhivanan Failing to plan is Planning to fail

To say directly what i need… I have to get the modified date n time automatically of a single record when it is changed. How can I do tht? I am not sure if i can do tht through trigger or any thing else.
Thanks!
"He laughs best who laughs last"

When you create a trigger, you don’t have to do anything special – just assume that there is a table called INSERTED when you’re in a trigger for insert or update, and there is one called DELETED when you’re in a trigger for update or delete. Not only can you assume that these special tables exist, you can treat them like any other table in any sort of query you want to write (but unfortunately they exist only inside triggers). The special tables have the same structure as your table "Quarter", except that TEXT and NTEXT columns will be missing. In a trigger for insertions, you only have the INSERTED special table. It contains only the records that were inserted. In a trigger for deletions, you only have the DELETED special table. It contains only the records that were deleted. In a trigger for updates, you have both DELETED, containing the data before the update, and INSERTED, containing the data after the update. Like I explained above, just use a join between Quarter and INSERTED – on the primary key column(s) – and update your Quarter table. It kind of looks like you’re not familiar with the full syntax of UPDATE queries: you can add a FROM clause with JOINs to bring in data from other tables besides the table you want to update. In this case, the join on INSERTED also filters the records that you need to update in your Quarter table. Now that wasn’t so hard, was it?

ok thanks a lot. I figured it out. Thanks!
"He laughs best who laughs last"

]]>