Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: Trigger on a view problem

    I am attempting to add a "instead of update" trigger on a view that joins two tables. Oracle will create the view and the trigger, however, when I attempt to add a record to the view I get an error. Here is the info;

    Create the View:
    Code:
    create or replace view gameView as
     select distinct g.gameId, g.season, g.week, g.stadium, 
     c1.team as team1, c1.score as Score1, 
     c2.team as team2, c2.score as Score2 
     from game g, competesIn c1, competesIn c2
     where c1.gameID = g.gameID 
     and c2.gameID = g.gameID
     and c1.team < c2.team
    Create the Trigger:
    Code:
    create or replace trigger addGameTrigger
     	instead of update on gameView 
     	referencing new as gv
     	for each row
     	begin
     		--create a record for the game info
     		insert into game
     			values(:gv.gameId,:gv.stadium,:gv.season,:gv.week);
     			--values(autonum.nextval,:gv.stadium,:gv.season,:gv.week);
     		
     		--create a record for each of the teams
     		insert into competesIn
     			values(:gv.gameId,:gv.team1,:gv.score1);
     		insert into competesIn
     			values(:gv.gameId,:gv.team2,:gv.score2);
     
     	end gameTrig;
     .
     run;
    Attempt to use the Trigger: (with fake data)
    Code:
    insert into gameView values(autonum.nextval,2004,7,'Steel Stadium','Steelers',35,'Jets',23)
    The following error results:
    ERROR at line 1:
    ORA-01732: data manipulation operation not legal on this view

    Any thoughts?
    I am new to Oracle so any suggestions you can give would be appreciated.

    Steve

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You have created an "instead of update" trigger. That doesn't fire when you try to insert into the view. Perhaps you meant "instead of insert"?

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    perhaps...


    Thanks.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •