Databases
StackOverflow2013
Postsdbo
POSyntax error with NEW while creating a trigger in PostgreSQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSyntax error with NEW while creating a trigger in PostgreSQL
    primarykey
    data
    text
    <p>I am creating a trigger in Postgresql as follows.</p> <pre><code> 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.'; </code></pre> <p>When executed in the Query Manager of pgAdmin-III, the following error is thrown.</p> <pre><code>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 </code></pre> <p>Can anyone tell me what is wrong with my code. I've searched everywhere.</p> <p>EDIT : Code for the procedure that is to be executed. This procedure has been successfully added to the database.</p> <pre><code>-- 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.'; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USRaghavendra Kumar
    1. USRaghavendra Kumar
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSyntax error with NEW while creating a trigger in PostgreSQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload