Hi ..

On Tue, 2005-09-13 at 08:06 +0900, rubyhacker / gmail.com wrote:
> If I can select a parent and trigger selects on the child
> automagically,
> then I should also be able to do an insert on the parent and trigger
> child inserts automagically.
> 
<Putting my Db hat on>
I am not sure that this quite right, and I am very happy to be corrected
here.

Hal, I think that you are talking, database-wise, about two different
aspects of relational database technology.  The first select refers to
views, the second to triggers.  So, assuming the tables foo and bar
(random SQL syntax ;-))

  table foo (
    id autoinc  not null     primary_key,
    name varchar(60),
  );

  table bar (
    id autoinc not null     primary key,
    name varchar(60),
    value integer,
    foo_id integer,
    FOREIGN KEY (foo_id) REFERENCES foo(id)
  );

Then we can create a view that selects automatically all the bars
associated with a foo (1-to-many) by

  create view foo-bar (Owner, Name, Value) as
    select foo.name, bar.name, bar.value
    from foo, bar
    where bar.foo_id = foo.id ;

So, the referential integrity is built-in using the foreign key
constraint.  This is not supported by all the major databases yet
(MySQL, for example, is supporting this in their next major release, I
believe).

Triggers are the next level of constraint and are usually defined on
insert, update or delete operations on tables.  So, for example, you
increment a counter on insert or make sure that all the child records
are removed on deletion.  So, for the example above, if we delete a foo
record, then all the bar records that refer to the deleted foo_id would
also be deleted.

So, triggers are usually a one-way street.  I am not sure that you would
be able to logically create an insert trigger on a child record unless
all the data in the child record could be derived from the parent, or
other tables within the database.  If this were the case, then it is
arguable that you even need to have that child record (unless it is a
pure housekeeping record, like a users login attempts, or that kind of
thing).

I hope that clears the waters a little ;-)

Regards,

-mark.