Instead of Trigger [message #662588] |
Sat, 06 May 2017 06:46 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I have a simple procedure like this:
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
call 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;
Why am I unable to call the procedure? I get an error when I compile the trigger. How it is to be done? I want to insert the data from the view into a table as soon as the data gets loaded into the "ol_temp" table.
|
|
|
Re: Instead of Trigger [message #662589 is a reply to message #662588] |
Sat, 06 May 2017 06:54 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote: I get an error when I compile the trigger. I wonder what the error is?
Apart from that, I do not understand what you are saying. Inserting into an ol_temp table will not trigger anything to do with a view.
|
|
|
|
Re: Instead of Trigger [message #662591 is a reply to message #662590] |
Sat, 06 May 2017 07:05 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Kaos, you will never get anywhere until you start to think. What is the connection between inserting rows into a table, and an INSTEAD OF trigger on a view? Are you under the impression that inserting rows into the table will cause the trigger to fire?
As for your error, do you not think that it would be helpful to mention the line number? Or perhaps even copy/paste what you are seeing?
|
|
|
Re: Instead of Trigger [message #662592 is a reply to message #662588] |
Sat, 06 May 2017 07:06 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
kaos.tissue wrote on Sat, 06 May 2017 07:46
Why am I unable to call the procedure?
Remove CALL statement. Even though CALL is SQL DML statement is isn't available in PL/SQL. And it is missing BEGIN:
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;
/
SY.
|
|
|
Re: Instead of Trigger [message #662593 is a reply to message #662591] |
Sat, 06 May 2017 07:12 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
The ol_temp table contains half data what I mentioned in the previous post. When the data comes into the OL_TEMP table, the view gets automatically updated completing the missing data. The view contains full data which I want to insert into the actual ORDER_LINE table. Inserting rows into the table will update the view and from the view I populate the ORDER_LINE table using the trigger. So, that's the connection between the view and the OL_TEMP table.
And as for the error its like this
Error starting at line : 1 in command -
CREATE OR REPLACE TRIGGER insert_ol
INSTEAD OF INSERT ON ol_ins
FOR EACH ROW
call insert_ol(:new.order_id,:new.prod_code,:new.qty,prod_cost,:new.prod_name,:new.palias);
end;
Error report -
ORA-00911: invalid character
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than
letters and numbers. $#_ are also allowed after the first
character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes
(q'#...#') cannot use spaces, tabs, or carriage returns as
delimiters. For all other contexts, consult the SQL Language
Reference Manual.
*Action:
[Updated on: Sat, 06 May 2017 07:15] Report message to a moderator
|
|
|
Re: Instead of Trigger [message #662594 is a reply to message #662593] |
Sat, 06 May 2017 07:16 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Oh dear. You don't understand what a view is, do you? It is nothing more than a SELECT statement. It doesn't store any data.
I remember friom previous posts that you don;t understand how to normalize data. It now seems that you don't understand some basics of SQL. You might be better off working with much simpler product.
|
|
|
|
|
|
|
Re: Instead of Trigger [message #662620 is a reply to message #662606] |
Mon, 08 May 2017 03:17 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
From what I understand of your process this isn't going to work.
You've got a temp table with partial data that needs inserting into order_lines, with the rest of the data in product.
You want a process that inserts into order_lines when data is added to the temp table.
This view isn't going to help.
Say I have a view:
CREATE VIEW web_orders AS
SELECT * FROM orders WHERE type = 'WEB';
Now if I write an insert:
INSERT INTO web_orders (<columns>) VALUES (<values>)
Oracle will automatically translate that into an insert into orders.
However if the view is more complicated and involves joins then oracle can't usually work out what to do when you try to modify the view.
That's why instead of triggers exist - to translate DML against the view into DML against the table(s) the view is based on.
As the others pointed out - views are stored queries, they hold no data, they aren't updated when the underlying tables are (unless you are talking about materialized views, but you're not).
So when data is inserted into ol_temp the view isn't updated and the instead of trigger doesn't fire.
The process that populates ol_temp should be running the code to insert into order_lines.
|
|
|
Re: Instead of Trigger [message #662631 is a reply to message #662620] |
Mon, 08 May 2017 06:29 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
I tried doing it. The view gets updated but the trigger doesn't fire. I inserted partial data into the ol_temp table. The data gets completed and immediately reflects in the view but he trigger does not insert data in the order_lines table. How do I do it? The two problems remaining in my problem are:
1. Inserting data into order_lines
2. Inserting data into Customer_Order table.
CREATE TABLE "CUSTOMER_ORDER"
( "ORDER_ID" NUMBER NOT NULL ENABLE,
"CUST_ID" NUMBER,
"PAYMENT_METHOD_ID" CHAR(2 BYTE),
"ORDER_DATE" TIMESTAMP (6) DEFAULT (sysdate),
CONSTRAINT "CUSTOMER_ORDER_PK" PRIMARY KEY ("ORDER_ID")
CONSTRAINT "CUST_ORDER_FK" FOREIGN KEY ("CUST_ID")
REFERENCES "CUSTOMER" ("CUST_ID") ENABLE
)
[Updated on: Mon, 08 May 2017 06:35] Report message to a moderator
|
|
|
Re: Instead of Trigger [message #662634 is a reply to message #662631] |
Mon, 08 May 2017 06:55 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
kaos.tissue wrote on Mon, 08 May 2017 12:29I tried doing it. The view gets updated but the trigger doesn't fire. I inserted partial data into the ol_temp table. The data gets completed and immediately reflects in the view but he trigger does not insert data in the order_lines table.
We all told you that.
Views don't store data, they are stored queries.
Think of them as complicated pointers.
You have a word doc on your pc and desktop shortcut to it - you update the doc, does that update the shortcut? Obviously not, but if you click on the shortcut you still see the updated doc.
Views are stored queries that provide a short-cut for looking up data. When you select from them oracle issues the view select against the tables and finds the data that matches at that point in time.
As I said, whatever inserts into ol_temp needs to do the insert into order_lines. So what does the insert into ol_temp.
|
|
|
|
|
Re: Instead of Trigger [message #662666 is a reply to message #662655] |
Tue, 09 May 2017 06:44 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
kaos.tissue wrote on Tue, 09 May 2017 00:59Yeah I got your point. But there's no query to insert data into the ol_temp.
I load te data into the ol_temp through a text file.
Of course there is no query to insert data into the ol_temp. There is no query to insert into anyone's table on any system. "Querys" to do not insert .. or update or delete. A "qeury" only SELECTs.
Pedantics aside, and taking your meaning even though your terminology is incorrect, there MUST be some sql statement to insert into the table. Your assertion that you "load te data into the ol_temp through a text file" simply means you have some utility (sqlldr?) that does the INSERT, rather than some procedure you wrote yourself. But regardless of if the INSERT is being done by typing at a keyboard in sqlplus, or executing a sql script that has the INSERT, or some anonymous or stored procedure, or a utility, the data is getting into the table with an INSERT statement. And any ON INSERT trigger on that table will fire.
Quote:But I guess I got the solution. I insert data into the ol_trmp and then fire a trigger
After inserting into the ol_temp to call a procedure. In the procedure I insert data into
The order_line. View is not needed then. In the procedure I can directly write the join query to complete the partial data and insert it.
Why does the trigger need to call a procedure? Why can't it just do the work itself? (There may be good reasons to have a trigger call a procedure, but given the way this thread has gone, I think it would be good for your own thought process to work through an explanation of why the trigger is calling a procedure, vs. just doing the work itself.)
|
|
|
|
|
|
Re: Instead of Trigger [message #662765 is a reply to message #662753] |
Thu, 11 May 2017 12:47 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
It's time to show a description on the 2 tables and the full text of the trigger on the ol_temp. This is a very simple task and your trigger must be incorrect. For example if the trigger on ol_temp is trying to insert into ol_temp it will cause a mutating error. It's time to paste in the trigger
|
|
|
Re: Instead of Trigger [message #662778 is a reply to message #662765] |
Fri, 12 May 2017 02:37 |
|
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
)
***The prod_alias is unique for every product. The data in OL_TEMP comes from a text file. The three values of OL_TEMP table are to be combined with the PRODUCT table on alias to get the remaining values for the ORDER_LINE table.
|
|
|
|
Re: Instead of Trigger [message #662822 is a reply to message #662802] |
Fri, 12 May 2017 10:13 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
EdStevens wrote on Fri, 12 May 2017 14:05...
I think you should close all three. Then step back and describe the business problem you are trying to solve, with no pre-conceived ideas of any specific technical solution. And creating a trigger, or creating a procedure is not a business problem.
+1
|
|
|