Dusan Kolesar wrote :
>> 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
For a table having a autoincrement column (datatype serial or default serial) you
may require the last value be means of the CURRVAL function applied to the
table name. Example :
CREATE TABLE TEST (k serial, s integer)
INSERT INTO TEST SET s = 1
SELECT TEST.CURRVAL FROM DUAL
or inside a db-procedure :
VAR lastId integer;
lastId = TEST.CURRVAL;
Best Regards,
Thomas
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
