Home » SQL & PL/SQL » SQL & PL/SQL » Trigger with error on insert
Trigger with error on insert [message #286465] Fri, 07 December 2007 09:22 Go to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Hello. Dont really know how I should explain a trigger problem to not make it to big but I will try my best:
I have done a trigger who says "No Errors" but when I try to INSERT values I get the following error:
Quote:

ORA-02291: integrity constraint (DBS36.SYS_C00625850) violated - parent key not found
ORA-06512: at "DBS36.ORDERCAR", line 15
ORA-04088: error during execution of trigger 'DBS36.ORDERCAR'

The goal is to have 3 tables:
- ordered: contains a "customer" and a "car" the customer has ordered
- maxOrders: contains a "car" and a "maximum number" of orders for that car
- waitToOrder: contains "customer" who waiting to be able to order a "car" and their "posstion" in line for that car.

When something is INSERTED into the "ordered" table and that car has reached it´s limit on maxOrders it should be placed into the "waitOrder" table instead.

The trigger who gave me this errors looks like this:
CREATE OR REPLACE TRIGGER orderCar
INSTEAD OF INSERT ON orderView
REFERENCING NEW AS new
FOR EACH ROW
DECLARE
   maxNum INT;
   currentNum INT;
   maxList INT;
BEGIN
   SELECT maxorderlist INTO maxNum FROM maxOrders WHERE car = :new.car;
   -- if the car exists into the maxOrders-list
   IF maxNum > 0 THEN
      SELECT COUNT(*) INTO currentNum
      FROM ordered
      WHERE car= :new.car;
      -- if there still is place to order
      IF currentNum < maxNum THEN
         INSERT INTO ordered VALUES(:new.customer,:new.car);
      -- else the maximum number of orders are set
      ELSE 
         SELECT MAX(maxorderlist) INTO maxList FROM maxorders WHERE car = :new.car;
         INSERT INTO waitToOrder VALUES(:new.customer,:new.car,maxList+1);
      END IF;
   -- else there is no maxlist in this case
   ELSE
      INSERT INTO ordered VALUES(:new.customer,:new.car);
   END IF;
END;


And the tables for the one who is intrested looks like this:
CREATE TABLE ordered(
customer VARCHAR(50),
car VARCHAR(20),
PRIMARY KEY(customer,car));

INSERT INTO ordered VALUES('John Green','bmw-1');
INSERT INTO ordered VALUES('Adul Dej','saab-3');

CREATE TABLE maxOrders(
car VARCHAR(50),
maxOrderList INT);

INSERT INTO maxOrders VALUES(
'bmw-1','1');

CREATE TABLE waitToOrder(
customer VARCHAR(50),
car VARCHAR(20),
waitPossition INT,
FOREIGN KEY(customer,car) REFERENCES ordered(customer,car),
PRIMARY KEY(customer,car));

CREATE VIEW orderView AS SELECT * FROM ordered;

Is it possible for anyone to see why I recive the error at top when I try to insert stuff in my table after created the trigger?
example of INSERT statement:
INSERT INTO ordered VALUES('Bill Gate','bmw-1');
Re: Trigger with error on insert [message #286467 is a reply to message #286465] Fri, 07 December 2007 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check what is constraint SYS_C00625850 then you can see why there is a violation.

Regards
Michel
Re: Trigger with error on insert [message #286472 is a reply to message #286465] Fri, 07 December 2007 09:55 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Ok, the error is gone. Have not done anything to make it go away. Very well, now the trigger dosent seems to work as I want it to. I have told it to. When I INSERT:
INSERT INTO orderView VALUES('Bill Gate','bmw-1');

The VALUES are INSERTED on "ordered" when it should be INSERTED in "waitToOrder". Are I supposed to do anything more to "start" the trigger or should the problem be the trigger itself?
Hard to work find whats wrong when there is no errors.
Re: Trigger with error on insert [message #286475 is a reply to message #286472] Fri, 07 December 2007 10:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well you are inserting into "orderView", which is a view on "ordered", so Oracle is doing exactly what you told it to do.

[Edit: Whoops, Sorry. didn't read the trigger code.]

[Updated on: Fri, 07 December 2007 10:03]

Report message to a moderator

Re: Trigger with error on insert [message #286476 is a reply to message #286465] Fri, 07 December 2007 10:05 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
I created the trigger like this:
Quote:

INSTEAD OF INSERT ON orderView

Should not this be possible? Have to make it INSERT on the table instead?
edit : ok, edit on edit. Any more suggestions? Smile

[Updated on: Fri, 07 December 2007 10:06]

Report message to a moderator

Re: Trigger with error on insert [message #286479 is a reply to message #286465] Fri, 07 December 2007 10:09 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Hunter_wow wrote on Fri, 07 December 2007 07:22

SELECT maxorderlist INTO maxNum FROM maxOrders WHERE car = :new.car;
......
-- else there is no maxlist in this case
ELSE
INSERT INTO ordered VALUES(:new.customer,:new.car);


This has nothing to do with your current error, but your assumption in the ELSE is not correct. If your SELECT from MAXORDERS does not find a match, an exception is raised, the else is never reached.
Re: Trigger with error on insert [message #286480 is a reply to message #286465] Fri, 07 December 2007 10:10 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
I didn't mean to send that last response yet...

Any suggestions? Yes, use a stored procedure rather than a trigger to control the flow of logic to eliminate the confusion of the trigger.
Re: Trigger with error on insert [message #286485 is a reply to message #286465] Fri, 07 December 2007 10:23 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Sorry but do you mean I should make a "Procedure" inside the trigger or replace the trigger? If so: dont I need to have a trigger to make actions when VALUES are INSERTED?
Re: Trigger with error on insert [message #286491 is a reply to message #286485] Fri, 07 December 2007 10:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I don't think the logic makes sense.

You have a external reference from "waitToOrder" to "ordered" on the customer / car columns.

So when the trigger redirects the insert into "waitToOrder" you run into the integrity constraint, because the customer and car are NOT in "ordered".

Bascially, the moment the trigger works as expected it can't work.

Re: Trigger with error on insert [message #286492 is a reply to message #286485] Fri, 07 December 2007 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
He meant, If I understand him, you replace both insert into the view and the trigger by a procedure and you call the procedure instead of doing an insert.

Regards
Michel
Re: Trigger with error on insert [message #286498 is a reply to message #286492] Fri, 07 December 2007 11:04 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Michel Cadot wrote on Fri, 07 December 2007 08:38

He meant, If I understand him, you replace both insert into the view and the trigger by a procedure and you call the procedure instead of doing an insert.


Yep
Re: Trigger with error on insert [message #286502 is a reply to message #286465] Fri, 07 December 2007 11:53 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Quote:

when the trigger redirects the insert into "waitToOrder" you run into the integrity constraint, because the customer and car are NOT in "ordered".

Sorry but I dont follow you. I never checks if customer and car is in ordered? Confused

After lots of work I get the error:
Quote:

ORA-04098: trigger 'DBS36.ORDERED' is invalid and failed re-validation
ORA-06512: at "DBS36.ORDERCAR", line 17
ORA-04088: error during execution of trigger 'DBS36.ORDERCAR'

When I checked how to handle the "ORA-04098 Error" I apparently should type:
Quote:

show errors trigger ORDERCAR;

But once I do this I´m getting:
Quote:

No errors.

Also the error "ORA-04088:" seems to be a runntime error and could not find out anything about how to fix this.
Re: Trigger with error on insert [message #286503 is a reply to message #286502] Fri, 07 December 2007 11:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Sorry but I dont follow you. I never checks if customer and car is in ordered?


Yes, you do :

CREATE TABLE waitToOrder
....
FOREIGN KEY(customer,car) REFERENCES ordered(customer,car),
....
Re: Trigger with error on insert [message #286504 is a reply to message #286502] Fri, 07 December 2007 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check why DBS36.ORDERED can't recompile.

Regards
Michel
Re: Trigger with error on insert [message #286505 is a reply to message #286465] Fri, 07 December 2007 12:19 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
"ThomasG"

Yes, you do :

Ah, true. Changed it to this but geting the same error as before:
CREATE TABLE waitToOrder(
customer VARCHAR(50),
car VARCHAR(20),
waitPossition INT);

"Michel"

Check why DBS36.ORDERED can't recompile.

it´s a table. How should I be able to find out why a table can't recompile? Gone throw the trigger code tons of times and I really cant see anything wrong about it. But it´s only the code in trigger who can be the evil in all this right?
Re: Trigger with error on insert [message #286506 is a reply to message #286505] Fri, 07 December 2007 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is also a trigger:
Quote:

ORA-04098: trigger 'DBS36.ORDERED' is invalid and failed re-validation

or you fake the message.

Regards
Michel
Re: Trigger with error on insert [message #286509 is a reply to message #286506] Fri, 07 December 2007 12:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have tested it here after creating everything with :

DROP TABLE ordered;
DROP TABLE maxOrders;
DROP TABLE waitToOrder;
DROP VIEW orderView;


CREATE TABLE ordered(
customer VARCHAR(50),
car VARCHAR(20),
PRIMARY KEY(customer,car));


CREATE TABLE maxOrders(
car VARCHAR(50),
maxOrderList INT);

INSERT INTO maxOrders VALUES('bmw-1','1');

CREATE TABLE waitToOrder(
customer VARCHAR(50),
car VARCHAR(20),
waitPossition INT,
PRIMARY KEY(customer,car));

CREATE VIEW orderView AS SELECT * FROM ordered;

CREATE OR REPLACE TRIGGER orderCar
INSTEAD OF INSERT ON orderView
REFERENCING NEW AS new
FOR EACH ROW
DECLARE
   maxNum INT;
   currentNum INT;
   maxList INT;
BEGIN
   SELECT maxorderlist INTO maxNum FROM maxOrders WHERE car = :new.car;
   -- if the car exists into the maxOrders-list
   IF maxNum > 0 THEN
      SELECT COUNT(*) INTO currentNum
      FROM ordered
      WHERE car= :new.car;
      -- if there still is place to order
      IF currentNum < maxNum THEN
         INSERT INTO ordered VALUES(:new.customer,:new.car);
      -- else the maximum number of orders are set
      ELSE 
         SELECT MAX(maxorderlist) INTO maxList FROM maxorders WHERE car = :new.car;
         INSERT INTO waitToOrder VALUES(:new.customer,:new.car,maxList+1);
      END IF;
   -- else there is no maxlist in this case
   ELSE
      INSERT INTO ordered VALUES(:new.customer,:new.car);
   END IF;
END;
/


And it seems to work in general :

SQL> INSERT INTO orderView VALUES('Bill Gates 1','bmw-1');

1 row created.

SQL> INSERT INTO orderView VALUES('Bill Gates 2','bmw-1');

1 row created.

SQL> INSERT INTO orderView VALUES('Bill Gates 3','bmw-1');

1 row created.

SQL>
SQL> SELECT * FROM ordered;

CUSTOMER                CAR
----------------------- -------------------
Bill Gates 1            bmw-1

SQL> SELECT * FROM waitToOrder;

CUSTOMER                CAR           WAITPOSSITION
----------------------- -----------   -----------
Bill Gates 2            bmw-1         2
Bill Gates 3            bmw-1         2


Of course "Waitposition" is never really incremented, so everyone gets maxList + 1 there.
Re: Trigger with error on insert [message #286510 is a reply to message #286465] Fri, 07 December 2007 12:45 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Surprised
There the problem was all the time and it works perfect now!!!

It´s my first trigger ever and I made some tests earlier and made incomplite triggers for "ordered" who I forgot. This non-functional triggers apparently made so I could not recompile the table.
What I did was just to drop the "ordered" table and created it once again with the trigger.

Tons of thanks for not giving up hope on me Very Happy

[Updated on: Fri, 07 December 2007 12:46]

Report message to a moderator

Re: Trigger with error on insert [message #286518 is a reply to message #286510] Fri, 07 December 2007 15:50 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Hunter_wow wrote on Fri, 07 December 2007 10:45

There the problem was all the time and it works perfect now!!!


I don't know about perfect...

SCOTT@LTI10G> INSERT INTO orderView VALUES('Bill Gates 3', 'pinto-1');
INSERT INTO orderView VALUES('Bill Gates 3', 'pinto-1')
            *
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.ORDERCAR", line 6
ORA-04088: error during execution of trigger 'SCOTT.ORDERCAR'
Re: Trigger with error on insert [message #286548 is a reply to message #286518] Sat, 08 December 2007 01:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I don't know about perfect...


At least the trigger works. Now only 99% of the job is left, getting the logic right. Wink
Re: Trigger with error on insert [message #286570 is a reply to message #286465] Sat, 08 December 2007 05:37 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Ah, thanks. Need to look into that.
But right now I also have done a trigger who INSERT the first "customer" from the "waitToOrder" to "ordered" when someone is DELETED from "ordered". It´s just one small problem I cant figure out:
When the first customer is DELETED from the "waitToOrder" the rest in the that list should get their "waitPossition" reduced by 1. First I did get one error who told me I was not allowed to have Column names as INSERT VALUES. To get rid of this error I did this:
SELECT car INTO currentCar FROM waitToOrder WHERE car = :old.car AND waitPossition = waitPossition;
SELECT customer INTO currentCustomer FROM waitToOrder WHERE car = :old.car AND waitPossition = waitPossition;
SELECT waitinglist INTO currentWait FROM waitToOrder WHERE car = :old.car AND waitPossition = waitPossition;

And then using insert and delete to make a new row with a waitingpossition one less and then delete the old possition:
INSERT INTO waitToOrder VALUES(currentCustomer,currentCar,currentWait-1);
DELETE FROM waitToOrder WHERE car = :old.car AND waitPossition = waitPossition;

But it seems like I´m just hiding the problem or something with this becouse the trigger creates succesful but when I try to DELETE anyone from the "ordered" I´m getting error telling me there was more then one row selected in the SELECT INTO statement. But this should not be possbile with the WHERE statement:
WHERE car = :old.car AND waitPossition = waitPossition;

But my guess is that I am doing it into the wrong way. Should I consider another solution for the problem?
Re: Trigger with error on insert [message #286571 is a reply to message #286570] Sat, 08 December 2007 05:50 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Definitely.

For the 'waitposition" I wouldn't use the real wait position, which would have to be changed when the customer moves up the list.

I would perhaps use a timestamp when the user was added in waitToOrder, and calculate the position he is in on the fly in the select statement.

If I think about it, I wouldn't use the ordered / waitToOrder layout at all, just an "ordered" table, and distinguish between the ones that should be "waitToOrder" in the select statement, too.

Or just have an "ordered" table, and two views which represent the ordered/waitToOrder. Which would also work better, if you decide to change the value in "maxOrders", which would not be possible now without breaking the data somewhat.

Too many complicated triggers make live difficult in the long run.
Re: Trigger with error on insert [message #286574 is a reply to message #286571] Sat, 08 December 2007 06:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Further note :

Try not to approach the problem from the angle that you want to have the data in tables that look like the data you wish to see.

Design the tables in a way that they represent the data you HAVE, and then worry about the way you display it later when you write the selects.

Also have a look at Database Normalization
Re: Trigger with error on insert [message #286578 is a reply to message #286465] Sat, 08 December 2007 06:58 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
ok, thanks. Just came to my mind UPDATE will be an appropriate command right now.
I have also tried to find a way to delete everything into the oracle database but google dose not give me anything. Any idea if it´s possible to drop all tables with one command?
Re: Trigger with error on insert [message #286579 is a reply to message #286578] Sat, 08 December 2007 07:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can delete the entire schema with

drop user DBS36 cascade;


if you want to delete everything created by that user.

Then you would have to re-create that user.

Re: Trigger with error on insert [message #286580 is a reply to message #286465] Sat, 08 December 2007 07:19 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Ok. I dont got any privileges to "drop user DBS36 cascade;".
I´m having problem droping talbes with constraints. Can anyone see why this dosent work. The talbe "tablename" apparently exists as you can see in the DROP statement:
Quote:

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tablename"

ERROR at line 1:
ORA-00904: "tablename": invalid identifier

DROP TABLE tablename

ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

I´m trying to check what I need to drop to be able to drop "tablename" ^^

[Updated on: Sat, 08 December 2007 07:21]

Report message to a moderator

Re: Trigger with error on insert [message #286581 is a reply to message #286580] Sat, 08 December 2007 07:27 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
In the select :

- single quotes not double quotes.
- upper case table name
Re: Trigger with error on insert [message #286586 is a reply to message #286465] Sat, 08 December 2007 08:55 Go to previous messageGo to next message
Hunter_wow
Messages: 31
Registered: November 2007
Member
Thanks. Worked great Smile
Re: Trigger with error on insert [message #286593 is a reply to message #286570] Sat, 08 December 2007 13:03 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Hunter_wow wrote on Sat, 08 December 2007 03:37

But my guess is that I am doing it into the wrong way. Should I consider another solution for the problem?

You are basically reinventing advanced queuing. Have you looked into that option?
Previous Topic: data insert problem
Next Topic: Returning control to main-body from procedure.
Goto Forum:
  


Current Time: Wed Dec 07 08:47:15 CST 2016

Total time taken to generate the page: 0.08451 seconds