| Subject | Re: Triggers to automagically add/modify/delete data in another table ? |
|---|---|
| Author | Adam |
| Post date | 2006-05-10T11:36:10Z |
--- In firebird-support@yahoogroups.com, Adriano <fadrianoc@...> wrote:
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
Country
(
ID,
Name
)
Contact
(
ID,
Name,
StreetAddress,
CountryID
)
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.
Adam
>because to
> Adam,
>
> >Are you familiar with using foreign keys and defining SQL joins,
> meunderstand these
> >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
> new concepts.leave the
>
> In effect i could avoid to duplicate data in these two table and
> data in Table A (there are many columns in it, in the example above i'veall the
> 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
> unique ID ?) i've also use triggers ?understand
> I'm reading here: http://makeashorterlink.com/?X3A1125A9 to well
> what you're talking aboutForeign 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
Country
(
ID,
Name
)
Contact
(
ID,
Name,
StreetAddress,
CountryID
)
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.
Adam