March 28, 2002 at 10:47 am
I want to use trigger or other method to implement the autonum function which means whenever a new record is added, my function must fill a value to the specified field of the same new record. And this value is calculate from the previous values of the same field in the same table.
The trigger approach seems good when you add record one by one, but when you add a set of rcords by using 'insert .. select .. from ..', the calculation just occured once not each time of each record.
Could somebody give me any help? Thanks in advance.
Cheers
Dev
Cheers
Dev
March 28, 2002 at 12:30 pm
You could use a user defined function or a cursor to achieve the results on multiple rows in one insert.
Andy
March 28, 2002 at 12:41 pm
Yes, I create a function to do that. It works fine when the insert number is less than 1000, but over 1000, the problem comes back. The incremantal value only was added once. It's very strange to me. I did the same thing in Oracle and met no problems. I think that maybe the SQL try to cache dml.
Do you guys have any suggestions about that?
Cheers
Dev
Cheers
Dev
March 28, 2002 at 12:45 pm
Could you post the trigger code and the function?
Andy
March 28, 2002 at 1:01 pm
My function code looks like the following:
-------------------------------------------
function Func as int
begin
...
select @seq=max(substring(id,3,8)) from TB1
set @seq = @seq +1
end
....
return @seq
-------------------------------------------
the sql statement is
------------------------------------------
insert into TB1 (F1,id) select V1, Func() from TB2
------------------------------------------
The purpose here is to insert the value to F1 from table TB2 and the id value based on the previous id value.
Hope to get some helps
Cheers
Dev
Cheers
Dev
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply