Suppose you want to generate the stored procedures for all of the tables in your database. This is no problem at all for MyGeneration. However, now suppose that a handful of those stored procedures need to write to an audit table when a record is deleted. There is no meta-data lying around in your DBMS system that you can access in your template that will relay this requirement, so you’re going to have to hand code the logic into the stored procedures. But what if you later change a column in one of those tables and have to regenerate the stored procedure which you have now hand edited? Will you lose your changes? Certainly not, you would protect any hand coded areas with a Preserve Region. Here is how.
First you must register your Preserve Region format with the template for the file type you are generating. You do so by giving it the file name of the original file on disk (usually derived from the table name during script execution) and the comment begin and end blocks. Everything between the comment begin and end blocks is preserved. In this case, you are trying to preserve hand coded comments in a SQL Server stored procedure so you would use SQL’s /* */ syntax. The Preserver Region is registered as follows.
- Code: Select all
output.setPreserveSource(filename, \"/*\", \"*/\")
After you have registered the Preserve Region format you are ready to use one or more Preserve Regions within the template. In this case the name of the Preserve Region is “AuditTrail”. To actually declare a Preserve Region you would use this syntax.
- Code: Select all
output.preserve(\"AuditTrail\")
This will result in the following code being injected into your output.
- Code: Select all
/*PRESERVE_BEGIN AuditTrail*/
/*PRESERVE_END AuditTrail*/
You can now edit your newly generated stored procedure on disk and add code within the Preserve Region. Notice that this is done in the generated code itself not the template.
- Code: Select all
/*PRESERVE_BEGIN AuditTrail*/
INSERT INTO AUDIT_TRAIL (@P1) VALUES (P1)
/*PRESERVE_END AuditTrail*/
The next time you regenerate the stored procedure anything within and including the Preserve Region will be preserved. You can even move the output.preserve(\"AuditTrail\") region around in your template and your custom code will move to that new location the next time you regenerate. If you remove the output.preserve(\"AuditTrail\") region from your template then the next time you regenerate that region will be gone. There is an example of this in the “HTML Database Report” template provided with MyGeneration, however it uses the HTML comment syntax.
