CODEWITHSUNDEEP
sqlpostgresqltriggers
Auutobot15
Auutobot15

Reputation: 47

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

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

Upvotes: 2

Views: 6582

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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

  1. 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;
  1. 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();

Upvotes: 4

user14063792468
user14063792468

Reputation: 956

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.

Upvotes: -1

Related Questions