PostgreSQL创建触发器时使用NEW出现语法错误

Question

PostgreSQL创建触发器时使用NEW出现语法错误

5

我正在PostgreSQL中创建一个触发器，如下所示。

    CREATE TRIGGER "PRODUCT_ADD_TRIGGER" AFTER INSERT
   ON product FOR EACH ROW
   EXECUTE PROCEDURE INVENTORY_ENTRY_NEW_PRODUCT(NEW.productcode);
COMMENT ON TRIGGER "PRODUCT_ADD_TRIGGER" ON product
  IS '-- Executes a procedure that inserts a row into the inventorytxns table.';

在 pgAdmin-III 的查询管理器中执行时，会抛出以下错误。

ERROR:  syntax error at or near "."
LINE 3: ... EXECUTE PROCEDURE INVENTORY_ENTRY_NEW_PRODUCT(NEW.productco...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."
SQL state: 42601
Character: 130

有人能告诉我我的代码哪里出了问题吗？我已经到处查找了。

编辑：要执行的过程的代码。此过程已成功添加到数据库中。

-- Function: "INVENTORY_ENTRY_NEW_PRODUCT"(integer)

-- DROP FUNCTION "INVENTORY_ENTRY_NEW_PRODUCT"(integer);

CREATE OR REPLACE FUNCTION "INVENTORY_ENTRY_NEW_PRODUCT"(productcode integer)
  RETURNS record AS
$BODY$
-- Declare the required variables.
DECLARE
    -- Inventory row that stores the result of the query on the inventory table.
    inventoryrow inventory%ROWTYPE;
    result record;
BEGIN

    SELECT * INTO inventoryrow from inventory where fk_productcode = productcode;

    IF NOT FOUND THEN
        -- INVENTORY TRANSACTION CAN BE ADDED.
        -- An inventory transaction can be added to the inventorytxns table
        INSERT INTO inventorytxns (fk_productcode,fk_txntypeid) VALUES(productcode,6);

        -- Once a row is added into the inventory txns table, it automatically adds data into the inventory table.
        SELECT * INTO result FROM INVENTORY WHERE fk_productcode = productcode;

        IF NOT FOUND THEN
            -- RAISE AN EXCEPTION. THIS OPERATION IS SUPPOSED TO HAPPEN.
            RAISE EXCEPTION 'Product with product code % added in inventorytxns table but not added in inventory table',productcode;
        END IF;
        -- Transaction type 6 represents addition of a new product transaction type.
    END IF;

    RAISE DEBUG 'Product with product code % already available in inventory',productcode;

END;
-- Check if the inventory entry already exits in the inventory table.
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "INVENTORY_ENTRY_NEW_PRODUCT"(integer)
  OWNER TO postgres;
COMMENT ON FUNCTION "INVENTORY_ENTRY_NEW_PRODUCT"(integer) IS 'Adds a new entry in the inventorytxns table for a new product.';

- Raghavendra Kumar

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- zero323 · Accepted Answer

您应该阅读官方文档中关于触发器过程的更多信息。当您调用触发器函数OLD、NEW以及一些其他内容或通过TG_NARGS和TG_ARGV隐式传递时，您不应该自行执行此操作。

CREATE OR REPLACE FUNCTION INVENTORY_ENTRY_NEW_PRODUCT()
RETURNS TRIGGER AS $INVENTORY_ENTRY_NEW_PRODUCT_TRIGGER$
BEGIN
  -- Here you can access NEW.productcode

RETURN NULL;
END;
 $INVENTORY_ENTRY_NEW_PRODUCT_TRIGGER$ LANGUAGE plpgsql;


CREATE TRIGGER PRODUCT_ADD_TRIGGER AFTER INSERT
ON product FOR EACH ROW
EXECUTE PROCEDURE INVENTORY_ENTRY_NEW_PRODUCT();

更新

所以在您的情况下，触发器应该像上面那样，触发器函数应该像下面那样：

CREATE OR REPLACE FUNCTION "INVENTORY_ENTRY_NEW_PRODUCT"()
  RETURNS record AS
$BODY$
-- Declare the required variables.
DECLARE
    -- Inventory row that stores the result of the query on the inventory table.
    inventoryrow inventory%ROWTYPE;
    result record;
BEGIN

    SELECT * INTO inventoryrow from inventory where fk_productcode = NEW.productcode;

    IF NOT FOUND THEN
        -- INVENTORY TRANSACTION CAN BE ADDED.
        -- An inventory transaction can be added to the inventorytxns table
        INSERT INTO inventorytxns (fk_productcode,fk_txntypeid) VALUES(NEW.productcode,6);

        -- Once a row is added into the inventory txns table, it automatically adds data into the inventory table.
        SELECT * INTO result FROM INVENTORY WHERE fk_productcode = NEW.productcode;

        IF NOT FOUND THEN
            -- RAISE AN EXCEPTION. THIS OPERATION IS SUPPOSED TO HAPPEN.
            RAISE EXCEPTION 'Product with product code % added in inventorytxns table but not added in inventory table',NEW.productcode;
        END IF;
        -- Transaction type 6 represents addition of a new product transaction type.
    END IF;

    RAISE DEBUG 'Product with product code % already available in inventory',productcode;

END;
-- Check if the inventory entry already exits in the inventory table.
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

来自文档

当PL/pgSQL函数作为触发器调用时，顶层块中会自动创建几个特殊变量。它们是：

NEW

数据类型为RECORD；在行级触发器中，它保存INSERT/UPDATE操作的新数据库行。对于语句级触发器和DELETE操作，该变量为NULL。

OLD

数据类型为RECORD；在行级触发器中，它保存UPDATE/DELETE操作的旧数据库行。对于语句级触发器和INSERT操作，该变量为NULL。

...

总结

你应该记住，触发器函数与“正常”的PostgreSQL函数非常不同，只能通过提供适当上下文的触发器进行调用。另一方面，NEW、OLD和其他与触发器相关的内容在触发器执行上下文之外完全没有意义。