MS SQL Triggers: how do i delete an updated value

[afrika]

hi,
I am working with a trigger. that responds to an update value.

PROBLEM
Once the table has been updated, i want to be able to delete the value, since

1. the trigger would insert new values into other tables
2. The updated table would be of no use.

Currently i am using this script below, but i am being adviced that using intermediate variables might return errors, esp. If there are more than one row updated

CREATE TRIGGER [PO_AGREEMENT_UPDATE] ON [dbo].[e_invoice]
FOR UPDATE
AS


IF UPDATE (STATUS)

declare @order2invoice int
select @order2invoice = (select invoice_num from inserted where usr = @user)

begin
--i have edited the code, but some scripts will be executed here

delete from e_invoice where invoice_num = @order2invoice

end

Any advice
Thanks
Afrika

[swampBoogie]

CREATE TRIGGER [PO_AGREEMENT_UPDATE] ON [dbo].[e_invoice]
FOR UPDATE
AS
IF UPDATE (STATUS)
--
-- 
--
delete from e_invoice where invoice_num 
in (select invoice_num from inserted where usr = @user)

[wwb_99]

Why not just run the insert on the other table then rollback the transaction, rather than deleting?

[afrika]

thanks for your comment,
i cant roll back the transaction, 'cos its an invoice table, generated from a shopping basket, i want to be able to delete it once the transaction is through

cherio !!!
Afrika