Good Practice for executing PSBUILD scripts

I have had change control call me several times in the past to restore a table because it was accidentally dropped when executing the SQL script generated using Application Designer. Whenever you use Application Designer to generate a script with “Alter by Table Rename” option checked, the script will have the following SQL’s

– Start the Transaction

– Create temporary table

– Copy from source to temp table

– CAUTION: Drop Original Table

– Rename Table

– Done

The risk of running this script As-Is is that if any of the above SQL encounters an error then the script will still proceed to run the next SQL in the script. So if there is an error while running any SQL prior to dropping the original table then we would have lost the table and all the data.

Hence the best practice for running any scripts created using Application Designer is to include ‘WHENEVER SQLERROR EXIT’ at the beginning of the script. This will ensure that the script will abort anytime there is an error. Making this part of our Change Control checklist has ensured that there is no reoccurrence of this issue.
Note:

Scripts generated using Change Assistant automatically have ‘whenever sqlerror exit’ at the beginning of the script.

Read More about  PSBUILD Scripts