CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Ulti
Ulti

Reputation: 11

Exception Oracle Instead of Update Trigger

I got one question with this Instead of Update Trigger:

CREATE OR REPLACE TRIGGER view_player_update
INSTEAD OF INSERT ON view_player
FOR EACH ROW
DECLARE
    anzTeams NUMBER;
    teamIdentifier NUMBER;
BEGIN
    SELECT count(*) INTO anzTeams FROM team WHERE team=:NEW.team;
    IF anzTeams = 0
    THEN
        INSERT INTO team(team) VALUES (:NEW.team);
        SELECT teamid INTO teamIdentifier FROM team WHERE team=:NEW.team;
    ELSE
        SELECT teamid INTO teamIdentifier FROM team WHERE team=:NEW.team;
    END IF;

    UPDATE player SET player=:NEW.player, position=:NEW.position,teamid=teamIdentifier WHERE playerid=:OLD.playerid;
END;

One case is wrong. By an Update of an Team

UPDATE view_player SET team='Real Madrid' WHERE playerid=1

i got this Exception:

ORA-01779: Kann keine Spalte, die einer Basistabelle zugeordnet wird, verändern

The Database cannot change the entry of an basistable?!

This is my code:

   CREATE TABLE team(
    teamid integer PRIMARY KEY,
    team VARCHAR2(100)
);


CREATE TABLE player(
    playerid integer PRIMARY KEY,
    player VARCHAR2(100),
    position VARCHAR2(100),
    teamid integer REFERENCES team(teamid)
);


INSERT ALL
  INTO team (teamid, team) VALUES (1, 'FC Bayern Muenchen')
  INTO team (teamid, team) VALUES (2, 'Manchester United')
SELECT * FROM DUAL;


INSERT ALL
  INTO player (playerid, player, position, teamid) VALUES (1, 'Manuel Neuer', 'goalkeeper', 1)
  INTO player (playerid, player, position, teamid) VALUES (2, 'Dante', 'defense', 1)
  INTO player (playerid, player, position, teamid) VALUES (3, 'Cesc Fabregas', 'midfield', 2)
  INTO player (playerid, player, position, teamid) VALUES (4, 'Lionel Messi', 'striker', 2)
  INTO player (playerid, player, position, teamid) VALUES (5, 'Arjen Robben', 'midfield', 1)
SELECT * FROM DUAL;


CREATE VIEW view_player AS
SELECT p.playerid,p.player,p.position, t.team FROM player p
INNER JOIN team t
ON  p.teamid = t.teamid;

I got a Sequence and Trigger for the Auto Increment of both Tables.

Thank you for you help!

Upvotes: 0

Views: 448

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

Think about this: when you run the update statement, what do you intend should happen? Should it:

  1. change the name of the team to which playerid 1 is joined to be 'Real Madrid', or
  2. change the teamid for playerid 1 to be that for Real Madrid?

The database cannot know what you want, and it is unlikely on its own to do either of those even if the intention was clear.

If you want to do the former then you should instead update the player tables.

If you want to do the latter then update the team table.

I think that this approach you're taking of using instead-of triggers on a view is a very bad practice, and you should stick to the conventional methods for maintaining the data.

Upvotes: 1

Related Questions