> From: Jim Starkey [mailto:jas@...]

Hi,

> If I remember correct, we had been talking about trigger templates
> and table inheritance.

Back to the real world :-)

> I proposed an alternative solution in which a base table containing,
> among other stuff, the fields last_update_date and last_update_user
> and the triggers to maintain those fields. Other tables could then
> "extend" the base table, inheriting both the fields and the triggers.
>
> Table inheritance, however, raises all sorts of interesting questions
> that beg answers before we can even start arguing about implementation
> stategy. Nice, thick, yummy sort of questions.

(I am not a C++ developer, so forgive me if one of my brain waves has
already been invented <g>)

I have two trains of thought on this one.

The first one is the requirement to have either

1) some form of global trigger that can be hooked up to one or more tables
and a small change to the SP/Trigger language that means you can do...

For i = 1 to Current_table.FieldCount
do
begin
if (old.field[i] <> new.field[i]) then
insert into this_table
(tablename, primarykey, username, timestamp)
values
(current_table.name, current_table.field[0], user, 'now')
end

2) The trigger template is some form of parameterised macro (in any
language, TCL comes to my mind), than would essentially be the same as
above, but when you call...

Trigger.Macro.Create(MyTableName)

... it creates a new trigger for the table, filling in the parameters with
real field names. This method could easily be done now in a client
application, Perl etc. (I do it my self in Delphi to some degree. But if
the SP language is to be enhanced anyway, it might be worth putting this in
the engine

(an after thought has just come to me, maybe these global triggers could be
hooked up to a domain?, maybe not)

My second train of thought is verging on 'OO heaven', where you have full OO
capabilities for all DB objects. You could have TableCustomer which
descends from TablePerson, imagine having private, protected and public
triggers! You could then create a base table and have a number of private
triggers, and some public ones that work in descendants. It would also mean
you could do things like add another AddressLine field to TableAddress, and
the extra field is available in TableInvoiceAddress and TableShippingAddress
(you would in reality only have one address table as an address is an
address, but it is only an example.

> Question 1: Does table inheritance simply mean attribute (fields,
> triggers, primary keys, indexes) inheritance, or does it mean a
> a unified semantic model? We could, of course, have both, but
> for the sake of elegance, lets assume one or the other.

If it is going to be done, I would go the whole way, and include everything.

> Question 2: Are primary key inherited? Choices: a) yes, b) no,
> c) declared primary key of extension table appended to primary
> key of base table (problem is that uniqueness of base table is
> problematic).

Yes, but allow them to be overridden in a descendant (as long as they where
declared as virtual <g>)

> Question 3: Is multiple inheritence necessary?

No, No and No, to put it simply. Interfaces, maybe (if possible), but not
MI.


Cheers

Phil