CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
Vanir
Vanir

Reputation: 9

How to write a trigger that checks an updated value in Oracle?

For an assignment I need to write an Oracle SQL trigger that prevents the StdBalance column in the STUDENT table from exceeding 500. Normally I would just use a check constraint to enforce this however I am being forced to write a trigger for it. As expected Oracle is throwing a mutating error due to me using SELECT in an update trigger and will not let me update the StdBalance value with anything. Does anyone have any idea how I could work around this? Here is the code for the trigger.

CREATE OR REPLACE TRIGGER Balance_Trigger
BEFORE UPDATE ON STUDENT 
FOR EACH ROW 
DECLARE 
Current_Balance NUMBER;
BEGIN 
SELECT :new.StdBalance 
INTO Current_Balance 
FROM STUDENT 
WHERE :new.stdID = StdID;
IF Current_Balance > 500
THEN Raise_Application_error(-20007, 'You cannot exceed an unpaid balance of $500'); 
end if;
end; 
/ 
show error;

Upvotes: 0

Views: 56

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

Just use :

Current_Balance := :new.StdBalance;

Instead of 

SELECT :new.StdBalance 
INTO Current_Balance 
FROM STUDENT 
WHERE :new.stdID = StdID;

to suppress the mutating error.

P.S. Even if such assignments are used in these cases, as David Faber warned, there's no need to return a value for Current_Balance, :new.StdBalance may be used directly in comparison for IF :new.StdBalance > 500 ....

Upvotes: 2

Related Questions