Re: Help in Database Triggers...

Rajasekhar Gunturu wrote at 8 Sep 1995 02:36:07 GMT
>
> Is it ok to have a procedure declared inside a database trigger?
> for example:
>
> /* ------- Code begins ------ */
>
> create trigger tu_test_ing on TESTING after update AS
> declare
> ch1 char(20);
> ch2 char(20);
> ...
> ...
> PROCEDURE check_val(i char(40),j char(40)) IS
> begin
> .....
> ch1 := i || ch1;
> ch2 := j || ch2;
> ...
> ...
> ...
> end;
> begin
> /* TRIGGER BODY */
> ........
> ........
> check_val(1,2);
> check_val(2,3);
> ........
> ........
> insert into TABLE values(ch1,ch2); -- Note the scope of the
> global vars 'ch1 and ch2'!!
> end;
>
> /* --------- Code Ends --------------------- */
>
> The above code doesn't seem to work(the compiler does not recognise
> the procedure declaration). Also, I'd like to have the values in
> variables 'ch1' and 'ch2' inserted into a table in the trigger body
> by calling the procedure 'check_val'.
>
> How do I go about doing it the right way?
>
> Thanks a lot...
>
> Rajasekhar
>
>

I don't check your code against my Oracle, but will note that your approach is quiete inefficient. Trigger will compile each times when fire, so your trigger 9if even work) will have substantial overhead. Creating of stored procedure and calling it from trigger is more efficient solution.

Alexander Alesinsky
JSV Investor Received on Thu Sep 21 1995 - 00:00:00 CEST