“AS"处或附近的语法错误在 postgresql 中创建触发器 [英] syntax error at or near "AS" in creating trigger in postgresql

问题描述

美好的一天，

我在查询时遇到问题，因为我似乎无法理解为什么我不断收到错误在AS"处或附近出现错误语法错误".这是我的查询:

I'm having trouble with my query, as I can't seem to understand why I keep getting the error "syntax error at or near "AS" here's my query:

    CREATE TRIGGER  updateAvailableQuantity
    AFTER INSERT ON sale_item FOR EACH ROW 
    AS $$
    BEGIN
        IF NEW.quantity > 0 THEN
        UPDATE products
        SET products.quantity_instock = products.quantity_instock - NEW.quantity WHERE barcode =  NEW.barcode;
        END IF;
    END;
    $$ LANGUAGE plpgsql;

我不断收到的错误是，

    ERROR:  syntax error at or near "AS"
    LINE 4: AS $$
            ^
    SQL state: 42601
    Character: 100

推荐答案

在 PostgreSQL 中不能这样写触发器.这是一个 2 步过程.

You can not write trigger in PostgreSQL in this way. Here its a 2 step process.

1. 首先创建一个触发器函数:

CREATE OR REPLACE FUNCTION updateAvailableQuantity()
RETURNS TRIGGER
AS
$$
BEGIN
    IF NEW.quantity > 0 THEN
    UPDATE products
    SET quantity_instock = quantity_instock - NEW.quantity WHERE products.barcode =  NEW.barcode;
    END IF;

RETURN NULL;
END;
$$ LANGUAGE plpgsql;

1. 然后在您的表上编写触发器以调用如下函数:

CREATE TRIGGER  trg_updateAvailableQuantity
AFTER INSERT ON sale_item 
FOR EACH ROW 
EXECUTE PROCEDURE updateAvailableQuantity();

这篇关于“AS"处或附近的语法错误在 postgresql 中创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！