当子查询如下>,> =,或当子查询用作表达这是不允许的
问题描述:
我有查询这样当子查询如下>,> =,或当子查询用作表达这是不允许的
CREATE TRIGGER check_shippeddate_tr
ON Orders
FOR UPDATE
AS
IF ((SELECT ShippedDate FROM inserted) > (SELECT RequiredDate FROM Orders))
BEGIN
PRINT 'Order must be shipped before required date'
ROLLBACK TRANSACTION
END
GO
然而,当我尝试更新表使用此代码
UPDATE Orders
SET ShippedDate = '1994-04-03'
WHERE OrderID = 11051
AND CustomerID = 'LAMAI'
AND EmployeeID = 7
它应该防止更新和打印邮件,因为ShippedDate在RequiredDate之后。但是,我得到这个错误“子查询返回的值超过1,当子查询跟随=,!=,<,< =,>,> =或当子查询用作表达式时,这是不允许的。”How do我修复它?
答
试着这么做.....
假设订单ID是主键列
CREATE TRIGGER check_shippeddate_tr
ON Orders
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM inserted i
inner join Orders o ON i.OrderID = o.OrderID
WHERE i.ShippedDate > O.RequiredDate)
BEGIN
PRINT 'Order must be shipped before required date'
ROLLBACK TRANSACTION
END
END
+0
谢谢,这个工作。 – Lenic 2015-04-01 23:17:27
答
你的桌子上创建一个检查约束
ALTER TABLE Orders
ADD CHECK (ShippedDate < RequiredDate)
的错误信息很简单:' SELECT foo FROM bar'返回结果的**表**而不是单个标量值。使用集合(例如'MAX'),添加一个'WHERE'条件来将其限制为1个结果,或使用'SELECT TOP 1'。 – Dai 2015-04-01 22:31:26
或者如果您打算对插入项目的ShippedDate> RequiredDate进行验证,您可能希望'SELECT RequiredDate FROM inserted'。 – AaronLS 2015-04-01 22:51:26
@戴我试过这样((选择TOP 1发货日期\t从插入)>(选择TOP 1 RequiredDate从订单)),当我尝试更新它。它始终激活触发器并打印消息。我该怎么做? – Lenic 2015-04-01 23:16:55