Triggers and Dynamic SQL...

Thanks to Obnoxio the Clown and Art S. Kagel... I think you're both right (in a kind of mutually exclusive sort of way)... I've found some (and possibly all) of the functionality I need is inherent in the structure of triggers (the BEFORE and AFTER clauses combined with FOR EACH ROW clause). In case it is only some (and not all) I'm interested to know how to call an ESQL\\C program from within a stored procedure. I can't find anything in the literature about it... CHEERS, Birdman Art S. Kagel wrote: Have the trigger call an SPL procedure that runs an ESQL/C external program. Or have the SPL create a new SPL with the desired text and fire it off. These are the only way in 7.xx. Obnoxio the Clown wrote: You can't. Greg Bird wrote: > > Hi, > Problem: I have to create triggers on a database that will execute > stored procedures utilising dynamic SQL and cursors etc. Because the > triggers are essential this would seem to rule out ESQL\\C. > Also, I have looked at the info for the Dynamic SQL blade that can > downloaded to allow Dynamic SQL in SPL and it seems to me to assume Version > 9. I am restricted to using Version 7.2 and 7.3... The blade (as far as I > can tell) requires the CREATE FUNCTION command (to install the blade) which > doesn't seem to be supported in the above Versions. > Does anybody know of any way to use Dynamic SQL in this situation? > Any ideas would be much appreciated... > CHEERS, > Greg > > Greg Bird > greg.bird@advdata.com.au

Greg Bird wrote: > Thanks to Obnoxio the Clown and Art S. Kagel... > I think you're both right (in a kind of mutually exclusive sort of way)... > > I've found some (and possibly all) of the functionality I need is inherent > in the structure of triggers (the BEFORE and AFTER clauses combined with FOR > EACH ROW clause). > > In case it is only some (and not all) I'm interested to know how to call an > ESQL\\C program from within a stored procedure. I can't find anything in the > literature about it... Caution : Performance in situation like this : trigger ->Stored Proc -> SYSTEM command is likely to be poor (the SYSTEM command has an overhead of around 0.5 seconds). Unless there's extraordinary functionality in your ESQL/C program and the trigger is going to be fired very rarely, you may want to reconsider the solution to your problem. Rudy