syntax error at or near "AS" in creating trigger in postgresql

Question

Good day,

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;

the error that I keep getting is,

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

Akhilesh Mishra · Accepted Answer · 2020-10-14 04:39:54Z

4

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

First create a Trigger function:

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;

Then write the trigger on your table to call the function like below:

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

answered Oct 14, 2020 at 4:39

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Auutobot15 Over a year ago

Thank you! Finally, it worked. I was in complete enigma as why this wasn't working and in mysql workbench, it works completely fine.

jorge is not ai · Accepted Answer · 2020-10-14 03:59:48Z

-1

You have an error in your syntax. See the documentation page on create trigger.

FOR EACH ROW can not go before REFERENCING AS statement.

The documentation also has a plenty examples.

answered Oct 14, 2020 at 3:59

jorge is not ai

1,0572 gold badges14 silver badges41 bronze badges

Collectives™ on Stack Overflow

syntax error at or near "AS" in creating trigger in postgresql

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related