TriggerExample_Advanced - elsif:old.grade!=:new.grade...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Let’s create a trigger on a dbst651_students to validate the grade change and  to print a grade change message. The valid grades are 'A', 'B', 'C', 'D', 'F'.  Consider 3 cases. 1. The new grade is different from the old grade and it’s valid. Proceed  with the update, and print a message that the grade has been changed. 2. The new grade is different from the old grade and it’s invalid. Prevent  the update. 3. The new grade is the same as the old grade. Print a message that the  grade has not been changed. create or replace trigger grade_change before update  of grade on DBST651_students for each row BEGIN     if :old.grade!=:new.grade and :new.grade in ('A', 'B', 'C', 'D', 'F')then         dbms_output.put('The grade was changed from '|| :old.grade);         dbms_output.put_line(' to '|| :new.grade);
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 2
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Background image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: elsif :old.grade!=:new.grade and :new.grade not in ('A', 'B', 'C', 'D', 'F') then dbms_output.put_line('The grade '|| :new.grade||' is invalid'); :new.grade:=:old.grade ; else dbms_output.put_line('The grade has not been changed'); END IF; END; / Since we want to validate the grade before it’s updated, we need to use a before update trigger. :new.grade is the update, and :old.grade is the grade we want to replace. When the grade is invalid, we set :new.grade=:old.grade In another words, the old grade is updated to the old value. Initial data Case 1: 'B'is a valid grade Case 2: 'H' is invalid grade Case 3: The grade doesn’t change...
View Full Document

This note was uploaded on 12/21/2009 for the course TRIGGER AD DBST 651 taught by Professor Yelenabytenskaya-ta during the Spring '09 term at MD University College.

Page1 / 4

TriggerExample_Advanced - elsif:old.grade!=:new.grade...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online