Trigger to select updated/inserted records only 1

[demivolt]

Dear subscribers,
>
> I have triggers attached to a number of tables in a vendor's database.
> When certain columns in these tables are updated or inserted to, the
> trigger fires a stored procedure that builds a table containing all records
> within a rather broad range. Because the database is proprietary, I cannot
> make column changes such as adding a modifydate field.
>
> How can I select only those records that were updated or inserted that
> fired the trigger in the first place? For instance, Is there a system
> table that points to an unseen DateStamp on each record? Should I build a
> temporary table holding "old" records to compare to new ones? If so,
> wouldn't this be just as time-and-resource-consuming as the way I'm doing
> it now?
>
> Your help will be greatly appreciated.
> Demivolt

 

[tlbroadbent]

FROM SQL Books Online:

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 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. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[demivolt]

Dear Terry,

Thanks for pointing me in the right direction. The chapter on triggers mentioned
the Inserted & Deleted tables as well as giving some examples, but apparently I was
mentally adrift when I read this part. I paid closer attention this time & now see my solution. Once again, thanks.