Triggers Question

[Jeremy Capp]

Hey every one. I have a Trigger question. Here's the senairo:

I have a database table called _1423 and another table called OLD_1423s.
When a record the _1423 table is updated via a Web page, I want that
record (before it's updated) to go into OLD_1423s as a new row.

I wrote a trigger for this, but when I update one row, it puts the
entire _1423 table into the OLD_1423s table.

How do I specify that I want the updated row only to be copied to the
OLD_1423s table? (my trigger code is below)
CREATE TRIGGER tr_archive_1423 ON [dbo].[_1423]
FOR UPDATE
AS

IF UPDATE(num_of_updates) AND @@rowcount=0 BEGIN
INSERT INTO OLD_1423s
(_1423_id,line_item,exhibit,category,system,contra ct_id,contractor_id,e
lin,title,subtitle,authority,reference,office,dd_r eq,app_code,statement_
abbr,frequency,as_of_date,first_date,sub_date,rema rks,addressees,drafts,
regs,repros,price_group,est_tot_price,prep_by,prep _date,app_by,app_date,
status,cat_other,dist_statement,addressees2,date_t ime_stamp)
SELECT
id,line_item,exhibit,category,system,contract_id,c ontractor_id,elin,tit
le,subtitle,authority,reference,office,dd_req,app_ code,statement_abbr,fr
equency,as_of_date,first_date,sub_date,remarks,add ressees,drafts,regs,re
pros,price_group,est_tot_price,prep_by,prep_date,a pp_by,app_date,status,
cat_other,dist_statement,addressees2,GetDate()
FROM _1423
RETURN
END

FYI - "num_of_updates" is a column in the _1423 table that tracks how
many times a particular row gets updated.

Your help & time is greatly appreciated.
- Jeremy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jeremy Capp (jc***@belzon.com) writes:

Hey every one. I have a Trigger question. Here's the senairo:

I have a database table called _1423 and another table called OLD_1423s.
When a record the _1423 table is updated via a Web page, I want that
record (before it's updated) to go into OLD_1423s as a new row.

I wrote a trigger for this, but when I update one row, it puts the
entire _1423 table into the OLD_1423s table.

How do I specify that I want the updated row only to be copied to the
OLD_1423s table? (my trigger code is below)

In a trigger you have access to two virtual tables, "inserted" and
"deleted". "inserted" includes the rows that were inserted for an
INSERT statement, and for an UPDATE table they hold the new values.
The "deleted" table holds the rows that were deleted by a DELETE
statement, and for an UPDATE statement this is where you find the
old values.

A common mistake is to assume that these tables only has one row, but
the trigger fires once per statement, so they can have many rows.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp