Dusan Kolesar schrieb:
Hello,
I have one master table:
CREATE TABLE "LOGFILE"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"DESCR" Varchar (255) ASCII DEFAULT '',
PRIMARY KEY ("ID")
)
and 2 detail tables
CREATE TABLE "MSG_IN"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"LOGFILE_ID" Integer DEFAULT -1,
"MSG" Varchar (4096) BYTE,
PRIMARY KEY ("ID")
)
CREATE TABLE "REQ_IN"
(
"ID" Integer NOT NULL DEFAULT SERIAL (1),
"LOGFILE_ID" Integer DEFAULT -1,
"REQ" Varchar (4096) BYTE,
PRIMARY KEY ("ID")
)
When a new row is inserted into MSG_IN or REQ_IN, there is a trigger
which decodes MSG or REQ into decsription and adds one new row into table LOGFILE.
Inside this trigger I want to set collumn LOGFILE_ID into new inserted ID value of LOGFILE table.
Is it posible, to find out new inserted ID?
I think in MySql LAST_INSERT_ID () makes this.
in my opinion this is bad design:
On OOP world your problem can be described by inheritance: LOGFILE is the base class, MSG_IN and REQ_IN are derived classes. On relational DBMS's one would implement this by three tables as above. MSG_IN and REQ_IN are subordinate to LOGFILE by referential integrity constraints with cascading delete, corresponding to the fact that they are detail tables. Referential integrity implies that the parent record must exist before the detail record.
To create / insert a complete derived entity you can choose different ways: Write a stored procedure like "CREATE_MSG_IN()" which does everything. Or: create a view for the join of LOGFILE and MSG_IN and place an "INSTEAD OF" trigger on it. The latter variant is of course dependent on this sprcific database feature.
Uwe
-- Uwe Schneider - WEB.DE AG - Amalienbadstr. 41 - 76227 Karlsruhe F&E / Database & Software Technologies - Tel. (+49) 721 94329 8320
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
