Home » SQL & PL/SQL » SQL & PL/SQL » Instead of Trigger (Oracle 11g R2, pl/sql)
icon5.gif  Instead of Trigger [message #662588] Sat, 06 May 2017 06:46 Go to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7622
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 #662590 is a reply to message #662589] Sat, 06 May 2017 07:01 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
I get ORA-00911: Invalid Character error.
Data into the ol_temp table comes in from a text file. There are some values missing. To get all the values I joined the 'OL_TEMP' table with the 'PRODUCT' table in a view. Now, from that view the complete data is to be inserted into the "order_line" table. For that i wrote a Trigger which gives me the error.

Ol_TEMP(order_id, qty, alias, pname). Using these I found out the values for the (prod_name, prod_price, prod_id) by joining it with the product table using the unique key "alias".
Re: Instead of Trigger [message #662591 is a reply to message #662590] Sat, 06 May 2017 07:05 Go to previous messageGo to next message
John Watson
Messages: 7622
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 7622
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 #662595 is a reply to message #662593] Sat, 06 May 2017 07:16 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
OMG I can't believe I missed BEGIN. Thanks!!!! its done. So embarrassing!
Re: Instead of Trigger [message #662596 is a reply to message #662595] Sat, 06 May 2017 07:19 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@John I very well know what a view is and what all operations can be done on it. I don't need to store data. The view I created is just to get the missing data and to fire a trigger to insert data into the order_line table, So that I just have to insert minimum amount of values into my text file and just load it into a table using sql loader. Rest of the process will be done by oracle automatically.
Re: Instead of Trigger [message #662598 is a reply to message #662596] Sat, 06 May 2017 07:20 Go to previous messageGo to next message
John Watson
Messages: 7622
Registered: January 2010
Location: Global Village
Senior Member
This is hopeless. Goodbye.
Re: Instead of Trigger [message #662606 is a reply to message #662596] Sat, 06 May 2017 13:06 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
kaos.tissue wrote on Sat, 06 May 2017 07:19
@John I very well know what a view is and what all operations can be done on it.
Obviously not.

Quote:
When the data comes into the OL_TEMP table, the view gets automatically updated completing the missing data.
No, the view does NOT "gets automatically updated". There is nothing to update. As John said, a view is nothing but a stored SELECT statement. You are saying that when data is inserted into OL_TEMP table, that a SELECT statement is "updated". That is one of the more absurd statements I've come across in my 35+ years in IT.
Re: Instead of Trigger [message #662620 is a reply to message #662606] Mon, 08 May 2017 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13292
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
kaos.tissue wrote on Mon, 08 May 2017 12:29
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.
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 #662655 is a reply to message #662634] Tue, 09 May 2017 00:59 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Yeah 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.

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.
Re: Instead of Trigger [message #662660 is a reply to message #662655] Tue, 09 May 2017 03:30 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
That's about what I had in mind. Glad it works for you.
Re: Instead of Trigger [message #662666 is a reply to message #662655] Tue, 09 May 2017 06:44 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
kaos.tissue wrote on Tue, 09 May 2017 00:59
Yeah 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 #662728 is a reply to message #662666] Wed, 10 May 2017 07:40 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@EDSTEVENS Absolutely. I am going to to that only. Thanks!!

Re: Instead of Trigger [message #662751 is a reply to message #662728] Thu, 11 May 2017 06:31 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
kaos.tissue wrote on Wed, 10 May 2017 07:40
@EDSTEVENS Absolutely. I am going to to that only. Thanks!!

mmm.
Now I'm seeing that this is just another angle on your other thread ...
Re: Instead of Trigger [message #662753 is a reply to message #662751] Thu, 11 May 2017 06:34 Go to previous messageGo to next message
kaos.tissue
Messages: 94
Registered: May 2017
Member
@EdStevens Yeah but still unable to solve the issue. I did write a trigger but it mutated the table. Surprised
Re: Instead of Trigger [message #662765 is a reply to message #662753] Thu, 11 May 2017 12:47 Go to previous messageGo to next message
Bill B
Messages: 1802
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 Go to previous messageGo 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
)

***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 #662802 is a reply to message #662753] Fri, 12 May 2017 07:05 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
kaos.tissue wrote on Thu, 11 May 2017 06:34
@EdStevens Yeah but still unable to solve the issue. I did write a trigger but it mutated the table. Surprised
So now you have three different threads running for essentially the same business problem.
This one,
and 'Trigger to join and insert data into table',
and 'Loading Data into multiple tables'

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.
Re: Instead of Trigger [message #662822 is a reply to message #662802] Fri, 12 May 2017 10:13 Go to previous message
Michel Cadot
Messages: 65849
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
Previous Topic: Trigger to join and insert data into table.
Next Topic: Error SQL: ORA-02270: no matching unique or primary key for this column-list
Goto Forum:
  


Current Time: Mon Oct 22 22:09:09 CDT 2018