问题描述
我是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触发器更新两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！