--- In firebird-support@yahoogroups.com, "Adam" <s3057043@y...> wrote:

>
> Shannon,
>
> Why not offer a Stored Procedure for this insert, and encourage the
> developers to use that instead. I would keep your trigger use to
> enforcing database consistency.

<snip>

This is precisely how I'm doing it at the moment, for exactly the
reasons you mention, but I wrote here a few days to ask for advice on
using triggers versus stored procedures so that I could provide a
particular interface to my users, and was told that triggers might be
a better approach. So I've been looking into using triggers, and I can
see their benefits in certain areas, but in other areas (such as this
one), they seem to make the whole thing rather unwieldy. At the moment
I have a certain degree of elegance in the way it functions, even if
it is mostly via SPs, and moving that functionality into triggers is
giving me some headaches that I'm not 100% sure I need :)
Anyway, thanks for your reply, and I might just stick with my SPs for
the time being (at least for this functionality). I can probably just
grant insert access on the tables to the SP and not give it to any
individual users. If they want to get data into those tables, they can
do it my way or not at all! Heh.
Cheers,
Shannon

> Adam.
>
>
> --- In firebird-support@yahoogroups.com, "shann0n110yd"

<sjlloyd@h...>

> wrote:
> >
> > Hi,
> > I have one main table which has about a half dozen foreign keys to
> > other tables to keep the data normalised, ie when a particular

VARCHAR

> > value is inserted into the main table, I want to check the

referenced

> > table for that value (which could be quite a lengthy string, and

which

> > could be repeated in the main table many times), and if it exists,
> > only store the integer primary key for that value from that

referenced

> > table in the main table. This is all fairly straightforward, but

how

> > do I handle the fact that even though I want the insert query to

pass

> > in a VARCHAR for a particular field in the main table, I want an
> > integer to eventually be inserted into that field (ie the integer
> > which corresponds to that VARCHAR in the foreign table). Can I

insert

> > a VARCHAR into an integer field, so long as my BEFORE INSERT

trigger

> > does the necessary work to locate the correct integer and use that
> > instead? I'd like my clients to be able to pass in strings via the
> > INSERT statement, but only end up storing integers in the table
> > itself.
> > Thanks,
> > Shannon