CODEWITHSUNDEEP
stored-proceduresoracle11gtriggers
Karan Gupta
Karan Gupta

Reputation: 529

Instead of trigger not firing

  CREATE OR REPLACE PROCEDURE insert_ol
 (
  p_ord_id order_line.order_id%type,
  p_pid order_line.product_id%type,
  p_qty order_line.quantity%type,
  p_price order_line.price%type,
  p_pname order_line.prod_name%type,
  p_alias order_line.prod_alias%type)

  IS
  BEGIN
  INSERT INTO order_line values (p_ord_id, p_pid, p_qty, p_price, p_pname, 
  p_alias);
  END;

And I have a INSTEAD OF TRIGGER like this:

 CREATE OR REPLACE TRIGGER insert_ol
 INSTEAD OF INSERT ON ol_ins
 FOR EACH ROW
 BEGIN
 insert_ol
 (:new.order_id,:new.prod_code,
 :new.qty,prod_cost,:new.prod_name,:new.palias);
 end;

The view on which the trigger is based is:

 CREATE OR REPLACE FORCE VIEW "OL_INS"
 AS
 SELECT ot.order_id, prd.prod_code, ot.qty, prd.prod_cost, prd.prod_name, 
 ot.palias 
 FROM ol_temp ot 
 JOIN product prd 
 ON ot.palias=prd.prod_alias;

When I insert data into the "OL_TEMP" table, the partial data gets joined with the "PRODUCT" table and gets into the view. But the trigger does not fire to call the procedure and insert data into the "ORDER_LINE" table.

What is the mistake I am doing and how to I correct it?

Upvotes: 1

Views: 372

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You seem to have things backwards, based on

When I insert data into the "OL_TEMP" table...

The instead of trigger fires when you insert into the OL_INS view, it will never fire if you manipulate the underlying table(s) directly. So you should be doing something like:

insert into ol_ins (order_id, prod_code, qty, prod_cost, prod_name, palias)
values ( ... )

The trigger will then fire, call the procedure, and insert data into the order_line table. Except it will error because your trigger refers to prod_cust (which isn't a known identifier) instead of :new.prod_cust.

From what you have described that isn't what you want at all though, and a trigger on the view doesn't make sense. You seem to want a simple insert, based on your temporary table and the permanent product table - it isn't even clear that you need the view at all:

INSERT INTO order_line (order_id, product_id, quantity, price, prod_name, prod_alias)
SELECT ot.order_id, prd.prod_code, ot.qty, prd.prod_cost, prd.prod_name, ot.palias 
FROM ol_temp ot 
JOIN product prd 
ON ot.palias = prd.prod_alias;

If you really do need the view for something else and want to use it here you still could:

INSERT INTO order_line (order_id, product_id, quantity, price, prod_name, prod_alias)
SELECT order_id, prod_code, qty, prod_cost, prod_name, palias
FROM ol_ins;

but I wouldn't create a view just for that. And you could wrap either of those inserts in a procedure if you really wanted to, but you would still have to call the procedure explicitly - not from a trigger, unless you used an after-statement trigger on the temporary table.

Upvotes: 1

Related Questions