Hi Dusan,
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
I agree, table design is not good. If I add DETAIL_TB_TYPE and DETAIL_TABLE_ID into table LOGFILE and if I remove LOGFILE_ID from REQ_IN, MSG_IN tables, then when I insert into REQ_IN or MSG_IN I can insert into LOGFILE table new row and I can set DETAIL_TB_TYPE and DETAIL_TABLE_ID into correct known values.
But it was only simple sample which helps me describe you my problem.
From your answer I assume that there is no way to find up value of autoincremen
column after insert.
Thank you wery much for your time and all your suggestions.
Regards, Dusan
-- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
