Home » SQL & PL/SQL » SQL & PL/SQL » Master Details Trigger problem in 10g XE App Express
Master Details Trigger problem in 10g XE App Express [message #224390] Wed, 14 March 2007 02:41 Go to next message
websmythe
Messages: 10
Registered: March 2007
Junior Member
I'm using Application Express in Oracle 10g XE - Express Edition for Windows.

I have a Master Detail Form with a parent & a child table that share the same key (project number), and am trying create a trigger to have the table automajically insert the current value of the parent key into the child key. The Trigger valids when I create it, but bombs when II run the form.

I know this is basic stuff, I have been doing my homework, looking everywhere I can think of, to no avail. I know its there, but just can't connect with the info I'm looking for. Any help, like where I should read about this would be much appreciated

Thanx

--------------
The error message I get when I run the form and try to ADD a record is...
[I]Error in mru internal routine:
ORA-20001: Error in MRU: row= 1,
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TANDC.MATERIALS_T1", line 7
ORA-04088: error during execution of trigger 'TANDC.MATERIALS_T1',
insert into "TANDC"."MATERIALS" ( "MAT_NUM", "PRJ_NUM","PRODUCT", "SUPPLIER")
values ( :b1, :b2, :b3, :b4 )

Error 	Unable to process update.[/I]

Table and Trigger definitions are:
-- --------------------------------------
-- "LEADSHEET" TABLE DEFINITION
-- --------------------------------------
create table  "Leadsheet"
(
    "prj_num" number(10,0),
    "prj_name" varchar2(48),
    constraint "leadsheet_con" primary key ("prj_num") enable
)
/

-- --------------------------------------
-- "LEADSHEET" TABLE TRIGGER
-- automajically adds increments prj_num
-- on insert & update of a record
--          WORKS JUST FINE!!!
-- --------------------------------------
create or replace trigger  "Leadsheet_T1"
before
insert or update on "leadsheet"
for each row
begin
if :new.prj_num is null then
    select leadsheet_seq.nextval into :new.prj_num from dual;
end if;
end;
/

-- --------------------------------------
-- "MATERIALS" TABLE DEFINITION
-- --------------------------------------
create table  "Materials"
   (
    "mat_num" number(10,0),
    "prj_num" number(10,0),
    "product" varchar2(32),
    "supplier" varchar2(32),
     constraint "materials_con" primary key ("mat_num", "prj_num") enable,
     constraint "materials_fk_con" foreign key ("prj_num")
     references  "leadsheet" ("prj_num") on delete cascade enable
   )
/

-- --------------------------------------
-- "MATERIALS" TABLE TRIGGER
-- automajically increments material number by 1
-- and adds the current project number from LEADSHEET
-- into project number in the MATERIALS table
-- on insert & update of a record
--       DOESNT WORK!!!
-- --------------------------------------
CREATE OR REPLACE TRIGGER  "MATERIALS_T1"
BEFORE
INSERT OR UPDATE ON "MATERIALS"
FOR EACH ROW
BEGIN

  DECLARE
    -- define a variable as the same "type"
    -- as the project number (key) column
    -- in the (parent) Leadsheet table

        prj_var leadsheet.prj_num%type;

  BEGIN
      -- if material number is empty

      IF :NEW.MAT_NUM IS NULL THEN

        -- increment material number by 1

        SELECT MATERIALS_SEQ.NEXTVAL INTO :NEW.MAT_NUM FROM DUAL;


        -- stuff the variable created above
        -- with the key value in the (parent) Leadsheet table

        SELECT PRJ_NUM INTO PRJ_VAR FROM LEADSHEET;

        -- stuff value in the variable into
        -- the project number (key) column
        -- in the (child) Materials table

        SELECT PRJ_VAR INTO :NEW.PRJ_NUM FROM DUAL;

    END IF;

  END;

END;
/

[Updated on: Wed, 14 March 2007 02:44]

Report message to a moderator

Re: Master Details Trigger problem in 10g XE App Express [message #224395 is a reply to message #224390] Wed, 14 March 2007 02:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since there is no where-clause in
SELECT PRJ_NUM INTO PRJ_VAR FROM LEADSHEET;
you will get more than 1 row.

Question: If there is more than one project, how on earth can the database know what project should be the parent of the inserted material??
Re: Master Details Trigger problem in 10g XE App Express [message #224399 is a reply to message #224390] Wed, 14 March 2007 02:58 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
"ORA-01422: exact fetch returns more than requested number of rows" means TOO_MANY_ROWS exception raises while your trigger
is executing. Look at the Oracle documentation in the part
of Exception Handling.

See on this SELECT statement without WHERE clause -
it's the source of problem, potentially it can return more
than one row at a time.

...
SELECT PRJ_NUM INTO PRJ_VAR FROM LEADSHEET;
...

And don't use SELECT INTO when you need to assign values
(except for teh sequence case):

SELECT <<si\omething>> INTO :NEW.<<column>> FROM DUAL;

Actually it has to be :NEW.<<column>> := <<something>>.

Rgds.
Re: Master Details Trigger problem in 10g XE App Express [message #224415 is a reply to message #224390] Wed, 14 March 2007 03:33 Go to previous messageGo to next message
websmythe
Messages: 10
Registered: March 2007
Junior Member
Thanks guys. I only been at the Oracle/SQL thing for about 2 weeks now, so even tho I can read error messages Razz I'm a thin it what its actually telling me cause I don't know what the default behavior should be. Thanks for pointing out the issues for me. I guess I should be a little more judicious about copying code to find answers from now on. Smile Embarassed
Re: Master Details Trigger problem in 10g XE App Express [message #224417 is a reply to message #224415] Wed, 14 March 2007 03:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No problemo!
As long as you try to find things yourself first (and show us you did) and keep your posts as clear as this one, we are always glad to help you out.

Welcome to the board!
Did you take a look at the two stickies yet? (by the look of your first post, you seem to know how to format your posts!)

[Updated on: Wed, 14 March 2007 03:42]

Report message to a moderator

Re: Master Details Trigger problem in 10g XE App Express [message #224513 is a reply to message #224417] Wed, 14 March 2007 08:41 Go to previous message
websmythe
Messages: 10
Registered: March 2007
Junior Member
Frank wrote on Wed, 14 March 2007 00:40
Welcome to the board!
Did you take a look at the two stickies yet?

Yup! Tryed to do my howmework first Smile
Previous Topic: sql help
Next Topic: Data model design
Goto Forum:
  


Current Time: Fri Dec 09 15:46:20 CST 2016

Total time taken to generate the page: 0.22887 seconds