-
Here is a trigger I used for updating a field when another field is changed or inserted.
Code:
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
-
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...
Code:
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 :)
