Microsoft SQL Server通过更改另一个表列来触发更新表行

I have two tables zipTbl and locationTBl with four common columns

我有两个表zipTbl和locationTBl有四个公共列

zipCode
City
stateCode
salesTaxRate

If the zipCode changes in the locationTbl say from '91311' to '10172' I want the city, stateCode, salesTaxRate in the locationTBL to be updated with the same values in the zipTbl where the zipCode is the new value '10172'

如果zipCode在locationTbl中从'91311'变为'10172',我希望locationTBL中的city,stateCode,salesTaxRate使用zipTbl中的相同值进行更新,其中zipCode是新值'10172'

zipCode is the primary key for the zipTbl and a foreign key in the locationTbl.

zipCode是zipTbl的主键和locationTbl中的外键。

I use the following to create a trigger but it only updates from the first row in the zipTBL not the row with the new zipCode

我使用以下命令创建一个触发器,但它只从zipTBL中的第一行更新,而不是使用新zipCode的行

CREATE TRIGGER trg_update_city_state on dbo.locationTbl
AFTER Update
AS 
BEGIN
     UPDATE locationTbl
     SET locationTbl.city = zipTbl.city,
         LocationTbl.stateCode = zipTbl.stateCode,
         locationTbl.salesTaxRate = zipTbl.salesTaxRate 
     FROM zipTbl
END

I have tried several combinations of adding "where locationTbl.zipCode = zipTbl.zipcode," but I keep getting syntax errors

我已经尝试了几种添加“where locationTbl.zipCode = zipTbl.zipcode”的组合,但我不断收到语法错误

2 个解决方案

#1

I think it's like this:

我想是这样的:

UPDATE locationTbl
SET locationTbl.city=zipTbl.city,
    LocationTbl.stateCode=zipTbl.stateCode,
    locationTbl.salesTaxRate=zipTbl.salesTaxRate
FROM locationTbl t1
JOIN inserted i
  ON t1.zipCode=i.zipCode
JOIN zipTbl
  ON i.zipCode=zipTbl.zipCode

#2

Have you tried something like

你尝试过类似的东西吗?

CREATE TRIGGER trg_update_city_state on dbo.locationTbl AFTER Update 
AS 
BEGIN 
  UPDATE locationTbl 
  SET locationTbl.city = INSERTED.city, 
  LocationTbl.stateCode = INSERTED.stateCode, 
  locationTbl.salesTaxRate = INSERTED.salesTaxRate 
  FROM INSERTED
  WHERE locationTbl.zipCode = INSERTED.zipcode

#1