Trigger to join and insert data into table. [message #662734] |
Thu, 11 May 2017 02:53 |
|
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 |
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 #662744 is a reply to message #662734] |
Thu, 11 May 2017 04:35 |
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 |
|
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 #662747 is a reply to message #662746] |
Thu, 11 May 2017 05:15 |
cookiemonster
Messages: 13943 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 |
|
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 #662754 is a reply to message #662748] |
Thu, 11 May 2017 06:34 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
kaos.tissue wrote on Thu, 11 May 2017 05:22I 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 |
|
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 |
cookiemonster
Messages: 13943 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 #662762 is a reply to message #662761] |
Thu, 11 May 2017 08:01 |
cookiemonster
Messages: 13943 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 #662783 is a reply to message #662779] |
Fri, 12 May 2017 03:26 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
kaos.tissue wrote on Fri, 12 May 2017 09:43Not 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 #662786 is a reply to message #662785] |
Fri, 12 May 2017 03:43 |
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 #662790 is a reply to message #662787] |
Fri, 12 May 2017 03:57 |
John Watson
Messages: 8951 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 |
John Watson
Messages: 8951 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 #662803 is a reply to message #662794] |
Fri, 12 May 2017 07:10 |
cookiemonster
Messages: 13943 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.
|
|
|
|