sql - 在 PostgreSQL 中创建触发器时出现 NEW 语法错误

Question

我正在 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.';

Answer 1

您应该在 official docs 中阅读更多有关触发程序的信息.当您调用触发函数 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 函数作为触发器调用时，会在顶层 block 中自动创建几个特殊变量。它们是:

NEW

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.

OLD

Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.

...

总结

您应该记住触发器函数与“普通”PostgreSQL 函数确实不同，它只能由提供适当上下文的触发器调用。另一方面是新的。老的。和其他触发器相关的东西在触发器执行上下文之外是完全没有意义的。