Oracle Triggers Query

Question

Lets consider a Table STUD and a ROW-Level TRIGGER is implemented over INSERT query.. My scenario goes like this, whenever a row is inserted, a trigger is fired and it should access some script file which is placed in the hard disk, and ultimately should print the result. So, is this thing is possible? and if yes, then this thing should exist in dynamic form, i.e. if we change the content of script file, then the oracle should reflect those changes as well.

I have tried doing this for java using External Procedures, but doesn't feel that much satisfied with the result that i wanted.

Kindly give your point-of-view for this kind of scenario and ways that this can be implemented.

Answer 1

A community wiki for reasons why this is a bad idea.

Reasons why using a file for dynamic code is dumb.

1. Each row being inserted has to open a file, read the entire contents, parse it and 'do something' with the result. That is going to be SLOW.

2. Depending on the OS environment, you may not be able to concurrently open the file for reading and writing. So you may find you have to shutdown the database to 'promote' code changes into the file.

3. Depending on the OS environment, you may even find that only one session can read from the file at a time.

4. Maybe the trigger will read the file in the middle of a 'save' and execute partial code.

5. File security will be totally separate from database security, creating a maintenance headache.

In short, storing the dynamic code in a table, rather than a file, would be a MASSIVE improvement.

Reasons why you shouldn't use dynamic code anyway:

1. Dynamic code hasn't been parsed/compiled. So it may not work and you don't find out until it gets executed.

2. Static code performs better than dynamic code because of reduced parsing overhead.

Misc. Reasons

1. Having a row level trigger read and execute the code means that, potentially, the same statement inserting multiple records into the table may pick up different versions of code to execute for different row insertions.