Introduction to Triggers

Speaker Notes

Slide 1:

Triggers are named PL/SQL blocks. In that way, they are similar to procedures, however procedures are involved with an explicit call and a passing of arguments while a trigger event causes the implicit execution of the trigger code and arguments are not involved. A trigger event is associated with DML such as INSERT, UPDATE or DELETE and when it is executed it is referred to as the "firing" of the trigger.

Slide 2:

This trigger, named trigger1, will be implicitly activated based on an update done to the table called new_donation_one.

After each update, I am collecting the sum of the contributions and putting it into total_notes. Following the keep in simple approach as opposed to the professional approach, I hardcoded the date into the code. In fact it would be better to have the system date or something similar in the date.

Note that after coding the trigger, it has to be created before use. I did that with @ trigger1.

You need to be clear on the fact that a trigger is tired to an event. If any code that I write encounters an update for new_donation_one, this trigger will be fired. That can be tremendously beneficial in creating an event trail.

Slide 3:

This is the code that does an update. The trigger shown on the previous slide is tied to updates on new_donation_one so when this update is encountered, the trigger is fired.

Slide 4:

More complex code would be needed to prevent this. However note that the sum did not change.

Slide 5:

The amount for 23456 used to be 45 - it has now changed to 99 because of the update done on contamt.

Slide 6:

In this example, I substituted sysdate for the hardcoded date to make a more realistic trigger.

Note that because I changed the trigger, I needed to use @trigger1 to create it again.

Slide 7:

Record 22222 used to have 10, it got updated to 50. Remember the update is based on a match to both idno and driveno which would make the records unique as long as two donations were not made by the same person to the same drive on the same date. When the update first the trigger, the new total is written with the system date. Note that the new total sums what is currently in new_donation_one. Since I changed 10 to 50, the total is 40 bigger.