A Trigger question

[Winla]

Here is a trigger I used for updating a field when another field is changed or inserted.


CREATE TRIGGER UpdateTax ON Customer
FOR INSERT, UPDATE, DELETE
AS
declare @Addr varchar(50)

Select @Addr=i.sAddress
from Customer a inner join inserted i on a.sCustomerID =i.sCustomerID

if (@Addr like '%U.S.A%')

begin

Update Customer set sTaxCode = 'USTax'
where exists ( select * from Customer a inner join
inserted i on a.sCustomerID=i.sCustomerID )

end



The problem is when I updated one record, all records' sTaxCode fld in the Customer table were updated. I cann't figure out why. Thx

[Mongo]

You may want to drop the DELETE, since it makes little sense to update to deleted records. Otherwise, using a bit of magic SQL polish...


CREATE TRIGGER UpdateTax ON Customer FOR UPDATE, INSERT AS
IF ( @@ROWCOUNT = 0 ) RETURN
IF EXISTS(SELECT * FROM inserted WHERE sAddress LIKE '%U.S.A%')
UPDATE Customer SET sTaxCode = 'USTax' WHERE sAddress LIKE '%U.S.A%'
RETURN

If it still mysteriously updates all records, check for an offending constraint. I hope this helps :)