SQL Server:标量函数不返回任何内容，但应返回INT

Question

这是我的函数：

CREATE FUNCTION fu_order_customer_adress (@customer_ID INT)
RETURNS INT AS
    BEGIN
        RETURN (
            SELECT c.adress_ID FROM customers AS c
            JOIN orders AS o ON (c.customer_ID = o.customer_ID)
            WHERE c.customer_ID = @customer_ID)
    END

它用于表orders的AFTER INSERT触发器，具有以下结构（没有不重要的列）：

orders (order_ID INT, customer_ID INT, employee_ID)

order_ID是主键，其他列是外键。

触发器看起来像这样：

CREATE TRIGGER tr_orders_insert ON orders
AFTER INSERT 
AS
BEGIN
    DECLARE @order_ID INT = (SELECT order_ID FROM INSERTED);
    DECLARE @customer_ID INT = (SELECT customer_ID FROM INSERTED);
    UPDATE orders
    SET adress_ID = (SELECT dbo.fu_order_customer_adress(@customer_ID))
    WHERE order_ID = @order_ID;
END

以下插页有编号，以便以后容易指向。

（1）这适用于具有不同customer_ID:的插入

INSERT INTO orders(customer_ID, employee_ID) 
VALUES (1, 1)
INSERT INTO orders(customer_ID, employee_ID) 
VALUES (2, 2)

（2）但当插入已使用customer_ID的订单时，插入将以错误结束：

INSERT INTO orders(customer_ID, employee_ID) 
VALUES (1, 2)

我发现这是由触发器中使用的函数引起的，因为在这种情况下，它什么也不返回。

我试图将函数中使用的SELECT function和SELECT放入触发器：

CREATE TRIGGER tr_orders_insert ON objednavka
AFTER INSERT 
AS
BEGIN
    DECLARE @order_ID INT = (SELECT order_ID FROM INSERTED);
    DECLARE @customer_ID INT = (SELECT customer_ID FROM INSERTED);
    SELECT c.adress_ID 
    FROM customers AS c
    JOIN orders AS o ON (c.customer_ID = o.customer_ID)
    WHERE c.customer_ID = @customer_ID)
    SELECT dbo.fu_order_customer_adress(@customer_ID)
    UPDATE orders
    SET adress_ID = (SELECT dbo.fu_order_customer_adress(@customer_ID))
    WHERE order_ID = @order_ID;
END

In（1）是两个SELECT的结果相同。

In（2）是SELECT的结果，但SELECT function不返回任何结果。

我不明白怎么了。。。谢谢你的帮助！

Answer 1

对于这个简单的触发器，我认为您不需要这个性能杀手Scalar函数，您可以简单地执行以下操作，而无需使用任何Scalar函数并简单地将表与Inserted表连接起来。

此外，触发器中的逻辑也有重大问题，它只适用于单个插入，如果订单表中有多个插入，则会失败。一种更安全、更高效的方法是。。。。。。。

CREATE TRIGGER tr_orders_insert ON orders
AFTER INSERT AS
BEGIN
  SET NOCOUNT ON;
    UPDATE O
       SET O.adress_ID = C.adress_ID
    FROM orders O 
    INNER JOIN inserted  AS I   ON O.order_ID    = I.order_ID 
    INNER JOIN customers AS c   ON C.customer_ID = I.customer_ID         
END