CODEWITHSUNDEEP
sqloracle-databasetriggers
Shane
Shane

Reputation: 2375

What is wrong with my Oracle Trigger?

CREATE OR REPLACE TRIGGER Net_winnings_trigger
  AFTER UPDATE OF total_winnings ON Players
  FOR EACH ROW
DECLARE
  OldTuple OLD
  NewTuple NEW
BEGIN
  IF(OldTuple.total_winnings > NewTuple.total_winnings)
  THEN
    UPDATE Players
    SET total_winnings = OldTuple.total_winnings
    WHERE player_no = NewTuple.player_no;
 END IF;
END;
/

I am trying to get a trigger that will only allow the 'total_winnings' field to be updated to a value greater than the current value.

If an update to a smaller value occurs, the trigger should just leave the set the value to the old value (as if the update never occured)

Upvotes: 1

Views: 124

Answers (2)

Justin Cave
Justin Cave

Reputation: 231881

Since you want to override the value that is specified in the UPDATE statement, you'd need to use a BEFORE UPDATE trigger. Something like this

CREATE OR REPLACE TRIGGER Net_winnings_trigger
  BEFORE UPDATE OF total_winnings ON Players
  FOR EACH ROW
BEGIN
  IF(:old.total_winnings > :new.total_winnings)
  THEN
    :new.total_winnings := :old.total_winnings;
  END IF;
END;

But overriding the value specified in an UPDATE statement is a dangerous game. If this is something that shouldn't happen, you really ought to raise an error so that the application can be made aware that there was a problem. Otherwise, you're creating all sorts of potential for the application to make incorrect decisions down the line.

Upvotes: 3

Craig
Craig

Reputation: 5830

Something like this should work.. although it will be hiding the fact that an update is not taking place if you try to update to a smaller value. To the user, everything will look like it worked but the data will remain unchanged.

CREATE OR REPLACE TRIGGER Net_winnings_trigger
BEFORE UPDATE OF total_winnings
ON Players
FOR EACH ROW
DECLARE
BEGIN
    :new.total_winnings := greatest(:old.total_winnings,:new.total_winnings);
END;

Upvotes: 0

Related Questions