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.

So assuming the following schema

maintable (Id, lookuptableID)
lookuptable (ID, longstring)

you could create a stored procedure that does something like this.

1. Search for LookupTableID based on your "longstring".
2. If none is found, generate a lookuptableid, insert the longstring,
and return the ID.
3. Generate maintableid
4. insert the maintableid, lookuptableid combination.

Using this sort of approach, you can deny the users access to the
tables directly, and instead give them stored procedures etc to use.
This abstraction of the developer from the database structure is often
very helpful in preventing silly mistakes that can so easily corrupt a
database.

It is the same as private and public variables and methods in
programming, you want your class to behave correctly, so you don't
want some guy in a rush to get something out the door to mess around
with your private variables and call methods in strange orders which
can cause all sorts of problems. Instead, you offer them public
methods and variables which they can safely use, and you can change
the internals if need be without them having to rewrite.

Back to the database, say you had another field in the maintable that
you wanted to normalise out into another table. You would need to
rewrite your insert queries, but if using a stored procedure, the call
hasn't changed. So as the dba, you can further normalise your database
as required without worrying about some program that might stop
working as a result.

You may also want to look at an updatable view. I am not sure if this
is possible as I have not needed to check them out as yet.

Hope that helps

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