SQL触发器更新两个表 [英] SQL Trigger that updates two tables

问题描述

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

I am new in SQL, can anybody please help me to fix the trigger bellow.

我有这两个表(评论"和报价")，我想在插入"，更新"和删除"中更新评论"表，以从报价"表中获取数据.如果更新成功，我想使用ReviewId和ReviewDate更新商品表.

I have these 2 tables(Review and Offer), I would like to update Review table ON Insert, Update and Delete getting the data from the Offer Table. And if this is successfully updated i would like to update Offer Table with the ReviewId and ReviewDate.

我正在使用Azure提供的SQL服务器.

I am using the SQL server provided by Azure.

UserKey和Asin是唯一值，因此这就是我用来链接这些表的内容. 我将非常感谢您的帮助！

UserKey and Asin are unique values so that's what I am using to link these tables. I will realy appreciate any help!

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 is what I have so far:

 // 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

推荐答案

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

Try the code below; hopefully it does what you're after; the logic's not exactly the same, but I've tried to guess what you're aiming for and code that:

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

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

NB: If you can explain your aim with this logic we may be better able to advise.

这篇关于SQL触发器更新两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！