"Martijn Tonies" <martijn@...> wrote in message
news:C1FE56B5C29FD211A99F00A0244BE8200CF84A@SRV_BISIT...

> 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>;

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

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.

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) 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).

> 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.

C.
--
Claudio Valderrama C. - http://www.cvalde.com - http://www.firebirdSql.org
Independent developer
Owner of the Interbase® WebRing