Re: And another one about PL/SQL

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3d1197f1$0$236$ed9e5944_at_reading.news.pipex.net>...
> "Rauf Sarwar" <rsarwar_at_ifsna.com> wrote in message
> news:c2d690f2.0206191405.b92b46_at_posting.google.com...
> > Since triggers are known to be very unstable, I would highly recommend
> > using PACKAGES.
>
> care to justify that comment. I certainly don't consider triggers (or
> standalone proceudres for that matter) unstable. Howard is using IMO exactly
> the right tool for the job.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************

I have recieved some emails beside replies to my post about "Triggers being unstable". I think I should have elaborated my dislike for triggers which I also think are unstable...__BUT__ I do want to put a disclaimer that when I say unstable, I __DO NOT__ mean that triggers will crash your database or application. They are unstable (In my view) when you have a big database (2000 + tables) and you tightly want to enforce business rules, Data integrity and at the same time tightly control access to base tables. It all depends how one wants to build the database and what kind of business rules will be applied. In some instances triggers will work better and in some instances, packages will work better...(There I said it!!!). For a small 100 to 200 table database, the impact is minimum as the objects can be easily controlled...but when you work with 2000 + table database like I do...the impact is very painfull. Here is why,

1. Object management. Imagine having atleast 3 triggers (Insert, Update and Delete) on each of the 2000 tables will give me a nightmarish 6000 triggers to deal with. On the other hand, if I control the data integrity and enfore business rules through Packages, I will only need to have one package per table. Much cleaner and easy to maintain.
2. Tight Access. I like to control access to my database through Packages. No access to base tables. Only client access is through Packages and Views. Even if somebody accesses the table from Sql*Plus or MS Access etc...they cannot do anything to the tables unless they have execute privilges on the Package controlling the table.
3. Data Integrity. When a Package that controls the table is Invalid or dropped, user will get a hard error the moment they try to access it. However, when a trigger goes dead for some reason or you forgot to enable the trigger after some maintenance, user or yourself will not know about it until much later when the damage has already been done.

Now these are purely my views...I am sure someone else will have pros and cons against using Packages but judging from above three situations...I much rather use Packages.

Anyone care to dispute this :)

Hope this clarifies it.
//RS Received on Thu Jun 20 2002 - 12:35:48 CDT