CODEWITHSUNDEEP
functionplsqltriggers
Laxmikant sahu
Laxmikant sahu

Reputation: 73

unable to call and insert value returned by function inside trigger , is there any thing i am missing?

I would like to use a trigger on table which will be fired every time a row is inserted, updated, deleted. I wrote something like this: unfortunately function i am calling is not working , and it gives java.sql.SQLSyntaxErrorException :ora-04098 : trigger Compiere3.test01 is invalid and failed re-validation.

CREATE OR REPLACE TRIGGER test001
   AFTER INSERT OR UPDATE OR DELETE
   ON m_transaction
   FOR EACH ROW
DECLARE
   Availableqty   NUMBER;
BEGIN
   Availableqty := BOMQTYAVAILABLE (:old.m_product_id, 1000000, 1000000);

   INSERT INTO MonitorTable (m_transaction_id, m_product_id, Availableqty)
        VALUES (OLD.m_transaction_id, OLD.m_product_id, Availableqty);
END;

Upvotes: 0

Views: 148

Answers (2)

Laxmikant sahu
Laxmikant sahu

Reputation: 73

finally i found the solution and the correct syntax is as follows :

CREATE OR REPLACE TRIGGER test001
   AFTER INSERT OR UPDATE OR DELETE
   ON m_transaction
   FOR EACH ROW
DECLARE
   Availableiqty   NUMBER;
BEGIN
   Availableiqty := BOMQTYAVAILABLE (:new.m_product_id, 1000000, 1000000);

   INSERT INTO MonitorTable (m_transaction_id, m_product_id, Availableqty)
        VALUES (:new.m_transaction_id, :new.m_product_id, Availableiqty);
END;

Upvotes: 1

Mike
Mike

Reputation: 607

In your column names clause, maybe Availableqty is supposed to be m_availableqty? Based on your apparent naming convention. It's expecting a column name and getting a variable instead.

Upvotes: 0

Related Questions