Обсуждение: [General] How could it work?

Suppose you have tables like this:
  table_1                tmp_store              tmp_article
+---------+----------+ +---------+----------+ +---------+----------+
| name    | type     | | name    | type     | | name    | type     |
+---------+----------+ +---------+----------+ +---------+----------+
| dat     | date     | | dat     | date     | | dat     | date     |
| store   | char     | | article | char     | | store   | char     |
| article | char     | | price   | money    | | price   | money    |
| price   | money    | | amount  | smallint | | amount  | smallint |
| amount  | smallint | +---------+----------+ +---------+----------+
+---------+----------+

and an insert like this:
insert into table_1 values ( '08/04/2002', 'aldi', 'hmilch', 0.57, 3 );

Is it possible for each insert
1. verify if a table exists with the name of the (store|article)
   if yes
       a. insert the relevant data
   if no
       a. create a table (store|article) like (tmp_store|tmp_article)
       b. insert the relevant data

I was reading about triggers and functions, but I havn't found an answer
how to create tables in triggers or in functions.

Markus

--
There is no spam like no spam..

On Tue, Apr 09, 2002 at 11:07:04AM +0200, Markus Orlich wrote:
> Suppose you have tables like this:
>   table_1                tmp_store              tmp_article
> +---------+----------+ +---------+----------+ +---------+----------+
> | name    | type     | | name    | type     | | name    | type     |
> +---------+----------+ +---------+----------+ +---------+----------+
> | dat     | date     | | dat     | date     | | dat     | date     |
> | store   | char     | | article | char     | | store   | char     |
> | article | char     | | price   | money    | | price   | money    |
> | price   | money    | | amount  | smallint | | amount  | smallint |
> | amount  | smallint | +---------+----------+ +---------+----------+
> +---------+----------+
>
> and an insert like this:
> insert into table_1 values ( '08/04/2002', 'aldi', 'hmilch', 0.57, 3 );
>
> Is it possible for each insert
> 1. verify if a table exists with the name of the (store|article)
>    if yes
>        a. insert the relevant data
>    if no
>        a. create a table (store|article) like (tmp_store|tmp_article)
>        b. insert the relevant data
>
> I was reading about triggers and functions, but I havn't found an answer
> how to create tables in triggers or in functions.

Umm, why create tables? Why not have a table with all the stores and a table
with all the articles? I think your database needs a little more design, as
the number of tables should remain almost constant during normal usage.

Besides, lots of small tables is rather in efficient.

HTH,

--
Martijn van Oosterhout <kleptog@svana.org>   http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing.  Speaking out against censorship and ignorance is the imperative
> of all intelligent people.