More on Triggers

Speaker Notes

Slide 1:

This slide show gives a little more information on triggers.

Slide 2:

The two tables being used are colstu and credit_range. The block of code we will be looking at is designed to insert records into colstu. However, the records are checked against credit_range to make sure that the credits are in the appropriate range for the student class level.

Slide 3:

Note that there is no way of telling that there is a trigger associated with the insert into colstu when looking at this anonymous block.

Slide 4:

This is a trigger that has been written to use before insert into the colstu table. It applies to all blocks of code that attempt to insert into colstu.

Please read the notes for definitions of clauses like BEFORE INSERT and FOR EACH NEW ROW. Also please read about :old and :new.

Slide 5:

The user enters input. When the INSERT is encountered the trigger is executed to determine if the range is correct. For this record the range is appropriate so no error message is raised.

Slide 6:

This attempt raised the error and the record was not inserted in the table. The BEFORE clause means the checking is done prior to the entry. RAISE_APPLICATION_ERROR is raising error 20001 with the message that the credits are out of range. The identification number and name are also shown. Notice that they are shown with the :new which is tied to the insert and the : is required.

Slide 7:

Note that senior is eliminated from the test in the insert so even although the number of credits - 60 - is not in the range of 91 to 999, no error was raised.