SQL触发器更新两个表

Question

我是SQL的新手，有人可以帮助我修复触发器问题吗？

我有这两个表（“评论”和“报价”），我想更新“评论”表，然后在“插入”，“更新”和“删除”中获取要约表中的数据。如果更新成功，我想使用ReviewId和ReviewDate更新商品表。

我正在使用Azure提供的SQL Server。

UserKey和Asin是唯一的值，所以这就是我用来链接这些表的东西。我将非常感谢您的帮助！

Review 桌子：

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

Offer 桌子：

 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

Answer 1

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

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

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