Re: Multiple "before insert" triggers on a table?

"Thomas T" <T_at_T> wrote in message news:<3ea813d9$1_at_rutgers.edu>...
>
> Rauf, so basically, you're saying that instead of using a "before insert"
> Trigger, I would just call a stored procedure directly, after the insert was
> complete? (Correct me if I misunderstood that.) The only problem I see
> with that is the inability to perform validation; if there was something
> wrong with the row I inserted, I'd then have to delete that row. Is there a
> workaround for this with a procedure package? Hm; I guess I could've called
> a procedure, let the procedure validate the information, and if all is
> well, -then- the procedure would perform the insert to the table?

Stored procedure is same as a Packaged procedure (Syntax wise). Only difference is that Packaged procedure is inside a package. Since you can Package number of different Functions/Procedures/Types etc in a Package... it becomes a life saver when it comes to code management.

Triggers or Stored/Packaged Procedures... It's just a different approach to the same problem. I prefer a Packaged procedure because you can handle everything inside one procedure (or multiple procedures in a package) to do data validation and actual inserts/updates/deletes... without involving any trigger. I prefer this because you can control access to the table by granting execute privilige on a package. There is no direct access to the table except ofcourse select... or even that can be handled by a VIEW. This way, no matter how you log into Oracle, you cannot run any direct DML's against the table. The only access is thru a package. This means Data will always be validated and you can use same procedure to do the job of your "After Insert" trigger.  

> Triggers are part of a user's schema, correct? If so, then I don't think
> disabling the triggers is possible; only the system uses the schema account.

What I meant by this was that in case Trigger was intentially disabled for maintenance, testing etc and was not enabled afterwards, then you run the risk of inserting bad data, specially if you are using the trigger for any data validation. This is by no means a common occurence but if it does happen...then damage could be severe. On the other hand, Package is either compiled or not...so you don't run any risk like you do in a trigger.

Regards
/Rauf Sarwar Received on Thu Apr 24 2003 - 17:21:06 CDT