Triggers

One of the last things I learned at my instructor led Oracle training class was trigger. Now I had worked a little with triggers before. However I learned more about them in training. A theme I got from my instructor was that derived data is bad. That means you should not use triggers to then update other tables based on data that just changed in the table with a trigger. Oooops. That’s exactly what we do with some of the triggers on my project. Then again, I never said we were not bad.

You can write either before or after triggers. After triggers are good for auditing. That is what we mostly use them for. Perhaps my project is not all that bad. You have the ability to change the new values for an update in a before trigger. We also do that on our project. However it usually feels kind of like a hack.

Here is another theme I got from my instructor. You should not use triggers to enforce integrity constraints. That is what constraints themselves are for. Oooops again. We do some integrity constraint checking within some of our triggers.

The source code for the triggers you compile into your database is stored in DBA_TRIGGERS. That sounds logical. It is best if the source for the triggers is very small. If you need to do complex work, have the trigger call another procedure that has the meat in it.

In retrospect, my company shelled out a lot of money for me to attend a week of PL/SQL training. This training alone did not prepare me for the certification test that I subsequently took. Nonetheless this training was very valuable. The material in the class was adequate. However the instructor’s knowledge was outstanding. I was able to ask a whole lot of questions. And I received excellent consultant style answers. Your mileage may vary. But I am one satisfied customer, even with the hefty price tag. I plan to attend some more training as soon as I can find some time within my busy schedule.