| use of a procedure in a trigger [message #38451] |
Thu, 18 April 2002 08:41  |
Anthony
Messages: 48
Registered: September 2000
|
Member |
|
|
I have a row-level trigger which is going to be fired on each modification or Insertion on table X. The table X has over a 100 fields and is going to write the contents nearly each one to another table.
3 questions:
1)Is there any space limitation on the size of triggers for tables in Oracle 8.0.6.?
2)I would like to put the code into a procedure (if only aethestic reasons!) and call the proc from the trigger. Could I still use the trigger keywords (ie. ":NEW.(fieldname)" in the proc? Is it worth doing this at all, taking into account performance reasons? (We have demanding users!)
3)For each record that gets updated I have to check a value in another table, whats the fast way of doing this, a view exists of both tables, can it be used in a trigger like this? or do I just use a join to do this?
Any help appreciated
Thanks
Anthony
|
|
|
|
| Re: use of a procedure in a trigger [message #38469 is a reply to message #38451] |
Fri, 19 April 2002 07:10  |
Jay Ramlakan
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
>1) ...space limitation on the size of triggers ...
Yep, but it's pretty big - 32K.
The Oracle 8i Application Developers Guide states: 'Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of PL/SQL code, then it is better to include most of the code in a stored procedure and call the procedure from the trigger.'
>2) ..use the trigger keywords (ie. ":NEW.(fieldname)" in the proc?...
Definately not. :NEW and :OLD are specific to triggers. You can pass values using :NEW as parameters to procs.
>3) ...For each record that gets updated I have to check a value in another table...join...
No, you won't be able to do a join. If you try to select from the table the trigger is on, you'll have 'mutating table' errors. Just select from the other table using :NEW values. Better still, put the check in a function passing :NEW as a parameter. Call the function from the trigger.
Regards,
-Jay
|
|
|
|
| Re: use of a procedure in a trigger [message #38605 is a reply to message #38451] |
Tue, 30 April 2002 02:10 |
Anthony
Messages: 48
Registered: September 2000
|
Member |
|
|
Thanks, I have also read that the trigger source code is not actually stored in the database. Instead the source code is recompiled each time the trigger is referenced. Sounds like a good reason to put everything in a procedure.
Is that still the case in Oracle 8.1.7?
TIA
|
|
|
|
| Re: use of a procedure in a trigger [message #38616 is a reply to message #38451] |
Tue, 30 April 2002 08:06 |
Jay Ramlakan
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
>"...trigger source code is not actually stored in the database.."
Source code is always stored in the DB - (unless it's loaded wrapped). As I recall, prior to version 7, trigger code was interpreted. Since then, triggers are compiled and the pcode executed. Compilation happens just once, not every execution.
Putting code in stored procedures makes it more modular and re-usable.
-Jay
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
