Help with a trigger

I have written the following trigger

alter trigger updatecustomerorderreleasedatewhenstatusisreleased
on CUSTOMER_ORDER
after update
as

declare
@.CUST_ORDER_ID VARCHAR(15)

select @.CUST_ORDER_ID = ID from customer_order
if update(STATUS)
insert into CUSTER_ORDER_RELEASE_DATE
(CUSTOMER_ORDER_ID,RELEASE_DATE)
values (@.CUST_ORDER_ID,getdate())

It always returns the same ID from customer_order not the one that is
changing status. What am I missing?[posted and mailed, please reply in news]

ANDY PHILLEY (APHILLEY@.WATSONFURNITURE.COM) writes:
> alter trigger updatecustomerorderreleasedatewhenstatusisreleased
> on CUSTOMER_ORDER
> after update
> as
> declare
> @.CUST_ORDER_ID VARCHAR(15)
> select @.CUST_ORDER_ID = ID from customer_order
> if update(STATUS)
> insert into CUSTER_ORDER_RELEASE_DATE
> (CUSTOMER_ORDER_ID,RELEASE_DATE)
> values (@.CUST_ORDER_ID,getdate())
> It always returns the same ID from customer_order not the one that is
> changing status. What am I missing?

You are reading the customer_order table with out a WHERE clause. SQL
Server will retrieve all rows, and the variable with recieve the last
value returned. Since there is no ORDER BY clause, this can be any
value in the table, although it's not truly random. It is not unlikely
that it is always the same row.

If you want to know rows that were affected, you should use the
the virtual tables "inserted" and "deleted". They hold the the after-
image and the before-image respectivly of the updated rows. Note that
since a trigger fire once per statement, there can be several rows in
the tables.

Your trigger should probably read:

CREATE TRIGGER trg ON CUSTOMER_ORDER AFTER UPDATE AS
IF UPDATE(STATUS)
INSERT CUSTOMER_ORDER_RELEASE_DATE (CUSTOMER_ORDER_ID, RELEASE_DATE)
SELECT i.ID, getdate()
FROM inserted i
JOIN deleted d ON i.ID = d.ID
WHERE i.STATUS = 'Relased'
AND d.STATUS = 'Something else'

I've here also taken care of the case that Status may be updated, but
not change.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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