Subject Re: Triggers to automagically add/modify/delete data in another table ?
Author Adam
--- In, Adriano <fadrianoc@...> wrote:
> Adam,
> >Are you familiar with using foreign keys and defining SQL joins,
because to
> me
> >anyway it appears to be simply what you are after?
> I'm really a beginner.
> I'm trying to reading and study a much as possible to well
understand these
> new concepts.
> In effect i could avoid to duplicate data in these two table and
leave the
> data in Table A (there are many columns in it, in the example above i've
> drastically simplify the scenario) all the data and then simply create a
> VIEW to catch the unique data i need from the 2 table.
> For other verse i could make a SQL joins.
> But what about foreign keys ?
> Could you make me an example of that ?
> Using foreign keys (i need a column in a third table where stored
all the
> unique ID ?) i've also use triggers ?
> I'm reading here: to well
> what you're talking about

Foreign keys are pretty simple to use and understand. In the master
table you have a field with a unique constraint, such as a primary key
field. In the detail table, you create a link to a record in the
primary key table.

Take a simple example of a contact database. You might have the table



You would declare a foreign key on the Contact (CountryID) referencing
Country (ID). This means that if you attempt to add a country that
does not exist, you will get an exception. Similarly, you can define
the behaviour when a record is deleted from country.

In your case, you seem to have a second table for which a record
references a single record in your first table. Rather than storing
all the information again in your second table, you simply store the
primary key of the first table in your second table, then join the two
tables together when you need it.

I am already off topic, the answer to your question is more about
database design than Firebird. Whilst I could provide a trigger to do
exactly what you suggest, it is a really bad way of doing it. Getting
the structure right is extremely important, because shortcuts today
are support calls tomorrow.

Google for some tutorials on SQL. Have a look at the Employee database
that ships with Firebird using an admin tool to see how it makes use
of foreign keys.