Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You should read more about trigger procedures in <a href="http://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html" rel="nofollow">official docs</a>. When you call trigger function OLD, NEW and bunch of other things or passed implicitly through TG_NARGS and TG_ARGV, so you shouldn't do it by yourself.</p> <pre><code>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(); </code></pre> <p><strong>UPDATE</strong></p> <p>So in your case trigger should look like above and trigger function like below: </p> <pre><code>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; </code></pre> <p><strong>From docs</strong></p> <p>When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:</p> <blockquote> <p>NEW</p> <p>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.</p> <p>OLD</p> <p>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.</p> <p>...</p> </blockquote> <p><strong>Summary</strong></p> <p>You should remember that trigger functions are really different from 'normal' PostgreSQL function and it can be called only by trigger, which provides proper context. For the other hand NEW. OLD. and other trigger related stuff is completely meaningless outside trigger execution context.</p>
    singulars
    1. This table or related slice is empty.
    1. POSyntax error with NEW while creating a trigger in PostgreSQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USzero323
    1. USzero323
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSyntax error with NEW while creating a trigger in PostgreSQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUndeletion
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COForgot to mention. I have already defined the procedure. I have updated the code in my question.
      singulars
      1. PO
      1. USRaghavendra Kumar
    2. COWhat I meant is you shouldn't pass args to trigger function this. They are available inside trigger function through TG_NARGS and TG_ARGV.
      singulars
      1. PO
      1. USzero323
    3. COCan you elaborate? Do you have any example?
      singulars
      1. PO
      1. USRaghavendra Kumar
 

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