Re: Help Creating trigger

ajaysm85_at_gmail.com wrote:
> I have written a SQL script containing many create/insert statements.i
> have to write a trigger which will be raised for failure of any
> statement in that script.can a trigger be written which will be raised
> for each statement and then the trigger code will check for the
> status(success or fail) of that statement?or can anybody suggest any
> proper solution for this?
>
> thanks in advance...

You could use a reporting table.

Create a table, SCRIPT_STATUS, with columns SCRIPT_NAME and STATUS.

After each statement, if it succeeds, INSERT into SCRIPT_STATUS (<SCRIPT_NAME> and "SUCCESS" or "FAILURE") - I don't know what your statements are doing so I can't suggest how you'd determine success or failure in each case.

Then, once all the routines are finished, select SCRIPT_NAME, STATUS from the table, and output it to a report file which would then be emailed to an administrator. You'd see a simple report, with all the script names, and whether they failed or not.

Or, select from the table where STATUS = 'FAILURE', and make a basic report out of that.

Dead simple to implement. Not particularly elegant.

BD Received on Fri Oct 13 2006 - 11:05:35 CDT