On Apr 24, 7:01 am, Lennart <Erik.Lennart.Jons...@[EMAIL PROTECTED]
> wrote:
> On Apr 24, 5:41 am, "Dave Hughes" <d...@[EMAIL PROTECTED]
> wrote:
>
>
>
> > Lennart wrote:
> > > On Apr 23, 10:21 pm, Oliver <JOHollo...@[EMAIL PROTECTED]
> wrote:
> > > > On Apr 23, 4:06 pm, Lennart <Erik.Lennart.Jons...@[EMAIL PROTECTED]
>
wrote:
> > [snip]
> > > > > If the problem is that you don't want to write the triggers by
> > > > > hand, I would suggest that you write a util in your favorite
> > > > > scripting language that creates the triggers for you
>
> > > > > /Lennart
>
> > > > Exactly, we don't want to maintain the triggers by hand, that's
> > > > exactly the point. That way, when changes occur to the table
> > > > structure, the associated trigger will still work without further
> > > > maintenance.
>
> > > I see, IMO it is better to generate static triggers during
development
> > > via some automatic script. A silly example:
>
> > [snip]
> > > I assume you have a list of tables that you want to audit. Unless
> > > there are milions of them it will only take a second or two to
> > > regenerate the trigger code.
>
> > This would certainly be my preference. Especially as "when changes
> > occur to the table structure" the associated triggers may get
> > invalidated and will need to be recreated anyway (depending on what
> > change occurred and how it was implemented).
>
> Exactly my thoughts too. As a matter of fact I do all my upgrades via
> a utility that among other things verifies this before a version is
> committed to the database. In case someone is interested I have an
> ASSERT procedure defined as:
>
> CREATE PROCEDURE TOOLBOX.ASSERT(stmt varchar(1000))
> LANGUAGE SQL
> BEGIN
>
> DECLARE tmpstmt varchar(1100);
> DECLARE not_found CONDITION FOR SQLSTATE '02000';
> DECLARE CONTINUE HANDLER FOR not_found
> SIGNAL SQLSTATE '77000'
> SET MESSAGE_TEXT = 'ASSERTION FAILED!';
>
> A: BEGIN
> -- Do nothing if drop session.tmp fails
> DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
> BEGIN
> END;
>
> DROP TABLE SESSION.TMP;
> END;
>
> DECLARE GLOBAL TEM****ARY TABLE SESSION.TMP (y int);
>
> SET tmpstmt = 'insert into session.tmp ' || stmt;
> execute immediate tmpstmt;
> END @[EMAIL PROTECTED]
>
> COMMENT ON PROCEDURE TOOLBOX.ASSERT IS 'Raises exception if stmt
> return 0 rows.
> Stmt must be of form select <int> from ... Note that sql string delim
> must be quoted' @[EMAIL PROTECTED]
>
> Before the version is commited to the database, the following call is
> always made:
>
> call toolbox.assert
> ('select 1 from lateral(values 1) x where not exists (
> select 1 from syscat.tables
> where tabschema in (
> <relevant tableschemas>
> ) and status <> ''N''
> union all
> select 1 from syscat.triggers
> where trigschema in (
> <relevant trigschemas>
> ) and valid <> ''Y''
> )' ) @[EMAIL PROTECTED]
>
> This way I will be notified that I messed something up, and the
> transaction is rolled back.
>
> /Lennart
I see your point about attended maintenance being the preferred
practice, as opposed to what I'm trying to do. Thanks for the insight.
|
