更新两个表的 SQL 触发器

我是 SQL 新手，有人可以帮我修复下面的触发器吗？

我有这 2 个表（评论和报价），我想更新评论表插入、更新和删除，从报价表中获取数据。如果更新成功，我想使用 ReviewId 和 ReviewDate 更新报价表。

我使用的是Azure提供的SQL服务器。

UserKey 和 Asin 是唯一值，因此我用它来链接这些表。 我将非常感谢任何帮助！

Review table:

 ReviewId | OfferId  |  Review  |  CustomerId  |  UserKey   |   Asin    |  ReviewDate
 ----------------------------------------------------------------------------------------
  25224      null        blah         null        12354ddd     123456     11/24/2014

Offer table:

 OfferId |  CustomerId  |  UserKey  |   Asin   |  ReviewId   |   ReviewDate |  Status
 ---------------------------------------------------------------------------------------------
  25224       55555        12354ddd    12345        null           null        Pending Review

这是我到目前为止所拥有的：

 // this trigger works, is there any other way to simplify this?
 CREATE TRIGGER dbo.InserUpdateReview
 ON dbo.Review
 FOR INSERT, UPDATE, DELETE
 AS

 IF( Select Review.OfferId from Review, Inserted Where Review.ReviewId = Inserted.ReviewId ) IS NULL

 BEGIN

 //Update Review table first, this update it is working

   UPDATE a
   SET a.OfferId =  ( Select Top(1) b.OfferId  From dbo.Offer b   Where b.UserKey = a.UserKey   AND  b.ASIN= a.ASiN AND b.ReviewId IS NULL ), 
    a.CustomerId   =  ( Select Top(1) b.CustomerId    From dbo.Offer b   Where b.UserKey = a.UserKey   AND  b.ASIN= a.ASiN AND b.ReviewId IS NULL )

FROM dbo.Review a
INNER JOIN Inserted i
ON a.ReviewId=i.ReviewId AND a.OfferId IS NULL AND a.CustomerId IS NULL 


   //update Offer table here,

  UPDATE Top(1) o
  SET o.ReviewId     =  (Select r.ReviewId  From dbo.Review r   Where r.UserKey = o.UserKey  AND  r.ASIN= o.ASiN AND  r.ReviewId = ins.ReviewId  ),
    o.ReviewDate   =  getDate()
 FROM dbo.Offer o
 INNER JOIN Inserted ins
 ON o.UserKey = ins.UserKey AND o.ASIN = ins.ASIN AND o.ReviewId IS NULL

 END

尝试下面的代码；希望它能满足您的需求；逻辑并不完全相同，但我尝试猜测您的目标并编写代码：

CREATE TRIGGER dbo.InserUpdateReview
ON dbo.Review
FOR INSERT, UPDATE, DELETE
AS
BEGIN

    --if it's an update
    if exists (select top 1 1 from inserted i inner join deleted d on d.ReviewId = i.ReviewId)
    begin

        --update the review table
        UPDATE a
        SET a.OfferId = max(b.OfferId)
        , a.CustomerId = max(b.CustomerId)
        FROM dbo.Review a
        INNER JOIN Inserted i
            ON a.ReviewId = i.ReviewId 
            AND a.OfferId IS NULL 
            AND a.CustomerId IS NULL 
        LEFT OUTER JOIN dbo.Offer b
            ON b.UserKey = a.UserKey   
            AND b.ASIN = a.ASiN 
            AND b.ReviewId IS NULL 

        --and the corresponding orders table
        UPDATE o
        SET o.ReviewId = max(r.ReviewId)
        , o.ReviewDate = getDate()
        FROM dbo.Offer o
        INNER JOIN Inserted ins
            ON o.UserKey = ins.UserKey 
            AND o.ASIN = ins.ASIN 
            AND o.ReviewId IS NULL
        LEFT OUTER JOIN dbo.Review r
            ON r.UserKey = o.UserKey  
            AND r.ASIN = o.ASiN 
            AND r.ReviewId = ins.ReviewId

    end

END

注意：如果您能用这种逻辑解释您的目标，我们可能会更好地提供建议。