Home » SQL & PL/SQL » SQL & PL/SQL » Trigger to join and insert data into table. (Oracle 11g R2, pl/sql)
icon5.gif  Trigger to join and insert data into table. [message #662734] Thu, 11 May 2017 02:53 Go to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
CREATE TABLE "OL_TEMP" 
   (	"ORDER_ID" NUMBER, 
	"QTY" NUMBER, 
	"PALIAS" VARCHAR2(10 BYTE)
   )

 CREATE TABLE "ORDER_LINE" 
   (	"ORDER_ID" NUMBER, 
	"PRODUCT_ID" NUMBER, 
	"QUANTITY" NUMBER, 
	"PRICE" NUMBER, 
	"PROD_NAME" VARCHAR2(50 BYTE), 
	"PROD_ALIAS" VARCHAR2(10 BYTE), 
	 
         CONSTRAINT "COP_PK" PRIMARY KEY ("ORDER_ID", "PRODUCT_ID")
  
	 CONSTRAINT "PROD_ORDER_FK" FOREIGN KEY ("PRODUCT_ID")
	  REFERENCES "PRODUCT" ("PROD_CODE") ENABLE
   )
   


CREATE OR REPLACE TRIGGER "INSERT_OL_TG" 
AFTER INSERT ON ol_temp
FOR EACH ROW
DECLARE
  v_oid  NUMBER;
  v_alias VARCHAR2(10);
  v_qty NUMBER;
BEGIN
  SELECT :new.order_id INTO v_oid FROM dual;
  SELECT :new.qty INTO v_qty FROM dual;
  SELECT :new.palias INTO v_alias FROM dual;
  
  INSERT INTO order_line (order_id, product_id, quantity, price, prod_name, prod_alias)
   (SELECT v_oid, prd.prod_code, v_qty, prd.prod_cost, prd.prod_name, v_alias FROM ol_temp 
         JOIN product prd ON (v_alias = prd.prod_alias));
END;

I want to insert data into ORDER_LINE table whenever a row is inserted into OL_TEMP table. The data in the OL_TEMP table is partial and needs to be completed before inserting into ORDER_LINE table. The trigger gives me the following error. How do I resolve it?




QL Error: ORA-04091: table OL_TEMP is mutating, trigger/function may not see it
ORA-06512: at "INSERT_OL_TG", line 10
ORA-04088: error during execution of trigger 'INSERT_OL_TG'
04091. 00000 - "table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
Re: Trigger to join and insert data into table. [message #662735 is a reply to message #662734] Thu, 11 May 2017 02:56 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
The error is pretty descriptive to be honest.

Quote:
A trigger attempted to look at a table that was in the middle of being modified by the statement which fired it.

*Action: Rewrite the trigger so it does not read that table.

Also this is a terrible idea for scaling, don't rely on triggers to handle what should be application logic.

[Updated on: Thu, 11 May 2017 02:57]

Report message to a moderator

Re: Trigger to join and insert data into table. [message #662736 is a reply to message #662734] Thu, 11 May 2017 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SELECT :new.order_id INTO v_oid FROM dual;
must be written
v_oid := :new.order_id

Never do that using trigger, this will NOT work as soon there is more than one concurrent transaction.

Re: Trigger to join and insert data into table. [message #662737 is a reply to message #662735] Thu, 11 May 2017 02:59 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yeah I know its bad for scaling but I have to do it. How do I rewrite the trigger without using that table? I need data from there only.
Re: Trigger to join and insert data into table. [message #662738 is a reply to message #662735] Thu, 11 May 2017 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Roachcoach wrote on Thu, 11 May 2017 09:56
...
don't rely on triggers to handle what should be application logic.
+1


Re: Trigger to join and insert data into table. [message #662739 is a reply to message #662737] Thu, 11 May 2017 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kaos.tissue wrote on Thu, 11 May 2017 09:59
Yeah I know its bad for scaling but I have to do it. How do I rewrite the trigger without using that table? I need data from there only.
As I said, this will not work.

Re: Trigger to join and insert data into table. [message #662740 is a reply to message #662738] Thu, 11 May 2017 03:00 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
How do I do it then?
Re: Trigger to join and insert data into table. [message #662741 is a reply to message #662740] Thu, 11 May 2017 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Concurrent transactions shouldn't really be an issue here as ol_temp is being loaded with external data via sqlloader. It's not something the users interact with.
Re: Trigger to join and insert data into table. [message #662742 is a reply to message #662741] Thu, 11 May 2017 03:38 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
yeah right but the problem still remains. Its a simple join statement to insert into ORDER_LINE but how and where to execute it so that the data is inserted? Sad
Re: Trigger to join and insert data into table. [message #662743 is a reply to message #662742] Thu, 11 May 2017 04:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
What precisely inserts into ol_temp?
Re: Trigger to join and insert data into table. [message #662744 is a reply to message #662734] Thu, 11 May 2017 04:35 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
Just think. Why are you querying OL_TEMP table (the one the trigger is on), as you do not use any column rom that table?

By the way, PROD_ALIAS should be at least unique in PRD table (you did not post its definition though); the foreign key on OL_TEMP.PALIAS and ORDER_LINE.PROD_ALIAS is missing though.
However, if its value is not present in PRD.PROD_ALIAS, you will insert 0 rows. Strange design and process flow.
Re: Trigger to join and insert data into table. [message #662745 is a reply to message #662743] Thu, 11 May 2017 04:35 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
sql loader inserts into ol_temp values of (order_id, quantity, alias). Take these three values and get values of (price, name, prod_id) from product table by joining it with alias as alias is unique and always present. Once you have all the 6 values, insert into order_line.

Can I do away with the ol_temp table and directly write a before insert trigger on order_line? Something like this:

CREATE OR REPLACE trigger insert_ol_tg
BEFORE INSERT ON order_line
FOR EACH ROW
BEGIN

SELECT prod_code, prod_cost, prod_name 
INTO :new.product_id, :new.price, :new.prod_name 
FROM product WHERE product.prod_alias= :new.prod_alias;

INSERT INTO order_line VALUES (:new.order_id, :new.product_id, :new.quantity, :new.price, :new.prod_name, :new.prod_alias);
END;
Re: Trigger to join and insert data into table. [message #662746 is a reply to message #662745] Thu, 11 May 2017 04:38 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@flyboy Yes the alias is unique in prd table but ol_temp is not related to order_line. Its just a temporary table.
Re: Trigger to join and insert data into table. [message #662747 is a reply to message #662746] Thu, 11 May 2017 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Definitely not like that. insert fires trigger, which does insert, which fires trigger, which does insert .....
Repeat until oracle sees what you've done and puts a stop to it by throwing an error.

If you modify :new.<whatever> in a before insert trigger then that value you've assigned is what gets written to the table. You don't need to do any DML.

And what runs sqlloader? You - manually, every time? A script? Some external process?

You want a procedure that sorts everything out. That procedure should be run by whatever runs sqlloader.
Re: Trigger to join and insert data into table. [message #662748 is a reply to message #662747] Thu, 11 May 2017 05:22 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I manually run it for now. I'll write a batch script soon. And yeah it gives me an error "maximum recursive values reached...". There should be something happening after I insert the data in the ol_temp table and sorts everything out. A procedure or a trigger that takes in the 3 values, gets the other three values and inserts the data in the order_line table.

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;

I need a procedure or anything to run the above query. The bold ones from the ol_temp table or the sql loader file and other from the product table.
Re: Trigger to join and insert data into table. [message #662750 is a reply to message #662748] Thu, 11 May 2017 06:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So write a procedure.
Then write a script that'll call sqlloader and then the procedure.
Re: Trigger to join and insert data into table. [message #662752 is a reply to message #662750] Thu, 11 May 2017 06:32 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
That's what I am unable to do. I don't know how to write a script (still got to learn that). That's why I wanted to write a trigger to do it. But the trigger causes the table to mutate. Even if I write a procedure which is called by the trigger then also it gets mutated. If I try to resolve the mutation problem then I can't join and insert into order_line.
Re: Trigger to join and insert data into table. [message #662754 is a reply to message #662748] Thu, 11 May 2017 06:34 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
kaos.tissue wrote on Thu, 11 May 2017 05:22
I manually run it for now. I'll write a batch script soon. And yeah it gives me an error "maximum recursive values reached...". There should be something happening after I insert the data in the ol_temp table and sorts everything out. A procedure or a trigger that takes in the 3 values, gets the other three values and inserts the data in the order_line table.

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;

I need a procedure or anything to run the above query. The bold ones from the ol_temp table or the sql loader file and other from the product table.
Now I'm seeing that this just another angle on your other thread.

Why a trigger at all?
Why sqlldr?
Instead of sqlldr, just define an external table on that file, and do the whole operation, much more sensibly, with a procedure that queries the external table, instead of loading the file into a temp table to drive a trigger that will never work.
Re: Trigger to join and insert data into table. [message #662755 is a reply to message #662754] Thu, 11 May 2017 06:39 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
"with a procedure that queries the external table". The external table will be same as the ol_temp table. It will be having partial values. I am not understanding what you just said at all.

The external table contains what columns? How do the missing values get completed? What does the procedure do? And how does the procedure gets called without a trigger?

I have a file having these values (order_id, qty, alias). I define an external table on this? Then call a procedure from where which will take these values, join it with product table and insert into the order_line table.

[Updated on: Thu, 11 May 2017 06:42]

Report message to a moderator

Re: Trigger to join and insert data into table. [message #662756 is a reply to message #662755] Thu, 11 May 2017 07:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've convinced yourself this is more complicated than it is.
The external table should just show the contents of the file.
Basic procedure would look like this:
PROCEDURE insert_order_lines IS
BEGIN

  INSERT INTO order_lines
  SELECT columns
  FROM external_table JOIN product ON .....

END;

As for how it's called - a file watcher is probably neatest.
Re: Trigger to join and insert data into table. [message #662757 is a reply to message #662756] Thu, 11 May 2017 07:23 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I am convinced that way because it has been days since I have been trying to achieve this. Anyways, I'll try doing this. So, I can't achieve it with triggers and temporary table? Or its just easier this way?
Re: Trigger to join and insert data into table. [message #662758 is a reply to message #662757] Thu, 11 May 2017 07:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You can be convinced & wrong at the same time.
Re: Trigger to join and insert data into table. [message #662759 is a reply to message #662758] Thu, 11 May 2017 07:38 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@BlackSwan Well said brother. Tell me can I achieve it by temporary table and trigger? or just external table? There are two more tables in which data is to be entered Wink. You guys helping a lot. Thanks Very Happy
Re: Trigger to join and insert data into table. [message #662761 is a reply to message #662759] Thu, 11 May 2017 07:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I have been watching this thread with total amusement & confusion.
I do NOT understand which objects exist & what data they contain as the starting point.
I do NOT understand exactly what the desired or expected results should be.
You seem obsessed & fixated on a "solution" that involves trigger & TEMP table.
I am baffled, perplexed & confused about how, what where, when & why EXTERNAL TABLE became part of the discussion.

Don't tell us about you perception of the problem.
SHOW us using SQL statements & results what you are starting with.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide


Re: Trigger to join and insert data into table. [message #662762 is a reply to message #662761] Thu, 11 May 2017 08:01 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's far easier to code everything that needs doing in a procedure than to use triggers.
It's far easier to follow the code that's been written six months later if it's in procedures rather than triggers.
The code will perform/scale better if it's in procedures rather than triggers.
If you simply want to load a set of data from a file into a single table as is, sqlloader is the best tool to use.
If you need to do additional processing to the data in the file before putting it in a table, using an external table is a lot easier - it gives you tonnes of flexibility.
Re: Trigger to join and insert data into table. [message #662763 is a reply to message #662762] Thu, 11 May 2017 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I do not understand the purpose for OL_TEMP table to start with.
Why is below not sufficient?
CREATE VIEW ol_temp (order_id, qty, palias) 
AS 
  SELECT order_id, 
         quantity, 
         prod_alias 
  FROM   order_line;
Re: Trigger to join and insert data into table. [message #662764 is a reply to message #662763] Thu, 11 May 2017 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
ol_temp exists to hold data from a file from an external system.
That data is then used to populate order_line, but a lookup on product is needed to get the prod_id.
Re: Trigger to join and insert data into table. [message #662779 is a reply to message #662764] Fri, 12 May 2017 02:43 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Not only the product_id but also other values like prod_price, prod_name since the OL_TEMP only has (order_id, qty, alias). So, I am going to make an external table and join it directly with product table in a procedure to insert into ORDER_LINE. Then how do I call the procedure upon insert on the external table?
Re: Trigger to join and insert data into table. [message #662780 is a reply to message #662779] Fri, 12 May 2017 02:46 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@BlackSwann I am going to make another thread on this. Maybe then it will be clear.
Re: Trigger to join and insert data into table. [message #662783 is a reply to message #662779] Fri, 12 May 2017 03:26 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
kaos.tissue wrote on Fri, 12 May 2017 09:43
Not only the product_id but also other values like prod_price, prod_name since the OL_TEMP only has (order_id, qty, alias). So, I am going to make an external table and join it directly with product table in a procedure to insert into ORDER_LINE. Then how do I call the procedure upon insert on the external table?
Which exact database action are you referring as "insert on the external table"? External table is just the external data source outside the database, so you cannot modify it from the database.

By the way, OL_TEMP is not declared as external in your first post. As you changed the triggered table from OL_TEMP to ORDER_LINE, you jumped into indefinite loop. I have no idea when the first insert statement to ORDER_LINE is called. Why are you not calling the procedure which sample cookiemonster posted from there? No trigger is needed.

Maybe, before writing any code, you should thoroughly analyze the requirements (call flow) and make the proper design (data model).
Re: Trigger to join and insert data into table. [message #662785 is a reply to message #662783] Fri, 12 May 2017 03:32 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
ol_temp was not originally external. Without a trigger how will the process be automated?

1. I make a external table.
2. I write the procedure to join it with product and add data into ORDER_LINE as cookiemonster said.

The procedure then has to be called explicitly which I don't want. I want that I insert data into the external table. Whenever I insert data into the external table, it gets reflected in the ORDER_LINE table.
Re: Trigger to join and insert data into table. [message #662786 is a reply to message #662785] Fri, 12 May 2017 03:43 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
You cannot INSERT data to external table from database. From wherever you do so, what is the problem in calling INSERT statament to ORDER_LINE table?

Or, are you not planning to "insert data into the external table" explicitly? How exactly?

Have you ever thought about making proper data flow analysis?
Re: Trigger to join and insert data into table. [message #662787 is a reply to message #662786] Fri, 12 May 2017 03:45 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I can add records in the file which is the external table. Not from the Database but from outside.

Example:

Say I have a file 'ol_temp' from which I create the external table.

Then I add data in the file (external table). I have a procedure to insert data into ORDER_LINE. How does the procedure gets called without a trigger??

[Updated on: Fri, 12 May 2017 03:48]

Report message to a moderator

Re: Trigger to join and insert data into table. [message #662789 is a reply to message #662787] Fri, 12 May 2017 03:57 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
The process is to be automated.

[Updated on: Fri, 12 May 2017 03:59]

Report message to a moderator

Re: Trigger to join and insert data into table. [message #662790 is a reply to message #662787] Fri, 12 May 2017 03:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
You never said previously that you might "add records in the file which is the external table". You have said in your many topics that the file as delivered to you. If you intend to edit it later, your mechanism becomes even more complicated: your procedure will have to identify the changes rather than process the whole file.

Much better to stay with your original model: when a file arrives, you read it as an external table to do the inserts into your ORDER_LINES table. The procedure is simple enough to write, and if you want it to run automatically, use the Scheduler to create a File Watcher that will launch the procedure whenever a new file is detected.

By the way, I wish you would not say "record" when you mean "row". No relational engineer does that.
Re: Trigger to join and insert data into table. [message #662791 is a reply to message #662789] Fri, 12 May 2017 03:58 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
SQL> create or replace directory as 'd:\ext';
create or replace directory as 'd:\ext'
*
ERROR at line 1:
ORA-22929: invalid or missing directory name

What did I do wrong now?
If you look up the syntax of CREATE DIRECTORY you will see that a directory name is required. As that error message tells you.
Re: Trigger to join and insert data into table. [message #662793 is a reply to message #662791] Fri, 12 May 2017 04:05 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@John Watson
By editing it I meant that the file arrives everyday with new data. Everyday a new file will come with new records in it. So, if I delete all the previous data in the file and then add new data will it work? Or I will lose all the data from the table in oracle also? Even if I lose the external table data, I will still have it in ORDER_LINE right?
And the scheduler and the file watcher part. Can you please tell me where will I find it?
Re: Trigger to join and insert data into table. [message #662794 is a reply to message #662793] Fri, 12 May 2017 04:08 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
And the scheduler and the file watcher part. Can you please tell me where will I find it?
The Scheduler is described in the Admninistrator's Guide, http://docs.oracle.com/cd/E11882_01/server.112/e25494/toc.htm
Re: Trigger to join and insert data into table. [message #662803 is a reply to message #662794] Fri, 12 May 2017 07:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lets clear something up. External tables are a lot like views. They don't actually hold any data what so ever. They're just pointers to the contents of a file.
If you overwrite the file with a new file then when you next query the external table it'll show the contents of the new file.
What ever you do to the file will have zero effect on the contents of any other table.
Re: Trigger to join and insert data into table. [message #662807 is a reply to message #662803] Fri, 12 May 2017 07:17 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
For those that may not have picked up on it, the OP is running no less than three threads on this problem.

'Trigger to join and insert data into table'
'Instead of Trigger'
'Loading Data into multiple tables'
Previous Topic: Pass two or more value
Next Topic: Instead of Trigger
Goto Forum:
  


Current Time: Fri Mar 29 06:03:44 CDT 2024