Hi Claudio,

> > without the new Firebird type, how can I recognize CHECK
> triggers for WITH
> > CHECK OPTION views? I can't figure it out... They don't appear in
> > RDB$CHECK_CONSTRAINTS and I cannot find them anywhere...
>
> With FB:
> select rdb$trigger_name from rdb$triggers
> where rdb$system_flag=5
> and rdb$relation_name = <tbl>;

Jups, got that one :)

> Without FB:
> select rdb$trigger_name from rdb$triggers
> where rdb$trigger_name starting with 'CHECK_'
> and rdb$relation_name = <tbl>;

This really is the only way?? I already filter check constraint
triggers via the RDB$CHECK_CONSTRAINTS table, that seems to
work fine, but I couldn't find anything for the view thingy...

> Can you see how silly is the metadata validation in pre-FB?
> Anybody can
> create an explicit trigger named CHECK_<nn> if he/she likes! Now, your
> second line of defense is that the DSQL layer simply writes
> the BLR for the
> trigger, it doesn't attempt to fill the source. Remember it's
> parsing the
> WHERE clause to create a filter that ensures that new values
> fit in the
> WHERE clause. Just put a procedure in the WHERE clause of the
> VIEW, use WITH
> CHECK OPTION and tell me what happens. <g> Hence, your second
> test may be
> that the source of the trigger is null, but I can wipe out
> the source of a
> manual trigger, too.

Yes, I know - nasty :) I guess I should go for:

1) the relation has to be a view
2) the trigger name is CHECK_<nn>
3) AND the source is NULL

Guess I'm pretty save then... :(

> The reason isql doesn't get confused is that it never looks at those
> triggers! Remember they are an implementation detail. By
> looking at the
> source of the view, you know that WITH CHECK OPTION was used (old IB
> versions write only WITH CHECK in the view's source, be
> careful)

Yes, noticed that too - haven't tried to re-compile such a view
yet... Probably fails too.

> so the
> check trigger is useless for you. However, the engine should
> care and since
> it can't be sure it's an automatic trigger, it can miss the
> boat. It's easy
> to mangle the engine by playing with some predefined names.
> This is why a
> numbering scheme is a must. Relying on some special names in
> the hopes that
> users won't clash with them is a silly game if you don't forbid these
> names... and they aren't forbidden. There are several cases
> apart from check
> triggers, like implicit domains and indices supporting constraints.
>
> You get a PRE STORE and a PRE MODIFY trigger since you could
> try to violate
> the VIEW's contraint in both cases. What I'm observing in
> rdb$dependencies
> after a test I did simply wipes out my sleep, but probably
> it's due to both
> triggers being the same thing (fired at different events) so
> only the PRE
> MODIFY one is tracked. Anyway, those dwarfs should be gone
> when the view is
> dropped. You shouldn't alter them directly (but you can... I
> tried to modify
> DYN to prevent that but I could stuff other things so I left
> the potential
> code as a comment only).

You can drop them too, tried it yesterday :) ... Guess you
get a view with a WITH CHECK OPTION that doesn't check :)

>
> > And I can't remember what the RDB$FLAGS column in
> RDB$TRIGGERS does either
>
> Forget that field. Among other things, it says if the trigger can run
> without security checks.

Without security checks? Some internal engine stuff or so?


Thanks for the explanation - I just haven't seen a view
with the CHECK OPTION before, so InterBase Workbench didn't
recognize them... And when I got to it, I noticed the
Firebird '5 value' and it seemed easy... Guess it isn't...

I wonder how long it will take before I have to fork my
code too :)


Thanks,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com


[Non-text portions of this message have been removed]