Firebird - add +1 to the column on insert lazarus / 16-Databases ---------------------------------------------------------------------- sin_dragan · 2014-12-11 12:40 Hi, I have FB 2.5.2 DB and unique column with trigger and generator. But if user cancels posting an ID is incremented. I would like to make one new column and to make generator that counts my rows or reads the ID from last row and increment by 1. How can I achieve that, I m looking for an elegant way? I know that I can do that via code in Lazarus, but I would like to do that on server side. ---------------------------------------------------------------------- exdatis · 2014-12-11 12:51 http://forum.lazarus.freepascal.org/index.php/topic,26694.msg164433.ht ml#msg164433 ---------------------------------------------------------------------- sin_dragan · 2014-12-11 13:17 Thx for your reply, but I need the generator to be able to read integer in the last record and increment by 1. For example ColumnID, ColToBeInc, 1112              1 1113              4 1115              5 On inserting new row ColumnID should be 1116 and ColToBeInc 6, if I delete that row and insert again the ColumnID will be 1117 and ColToBeInc 6 again < this is what I need. ---------------------------------------------------------------------- Never · 2014-12-11 15:36 this might help can't test it now but i'm sure is not far from what you are asking for Code: [Select]CREATE TRIGGER [name] FOR [table] ACTIVE BEFORE INSERT ..... ............ DECLARE VARIABLE MYMAX INTEGER; BEGIN MYMAX =0; SELECT MAX([field]) FROM [table] IF NEEDED--->>  WHERE [A FIELD]=[AVALUE]   INTO MYMAX ;  MYMAX =MYMAX +1;   IF (NEW.[field] IS NULL) THEN     NEW.[field] = MYMAX ;   .................... .................. END ---------------------------------------------------------------------- goodname · 2014-12-11 17:51 I don't think that Never's method is transaction safe. Two transactions may start running the trigger in parallel and get the same value for MYMAX. This would lead to which ever transaction ends last generating a invalid value. Firebird uses sequences to eliminate this problem but this is something that I think your trying to avoid. ---------------------------------------------------------------------- Never · 2014-12-11 18:19 since the [field] is set as unique int i don't think there will be any faults at least  i never had one with several types of dbs with trigers before or after ---------------------------------------------------------------------- goodname · 2014-12-11 18:37 Quote from: Never on December 11, 2014, 07:19:25 pmsince the [field] is set as unique int i don't think there will be any faults at least  i never had one with several types of dbs with trigers before or after Well if is not transaction safe then duplicate values can be generated and the unique constraint error would have to be caught so the whole transaction could be reapplied. It is possible to do just could be a lot of work to implement. This is one of the reasons sequences exist as they avoid this whole mess. ---------------------------------------------------------------------- Never · 2014-12-11 18:55 you are apsolutely right about sequenses but what if there is a reason to avoid them...?! if you do not trust the option provided you can temporary serialize transactions providing a lock with an update ---------------------------------------------------------------------- goodname · 2014-12-11 20:04 Quote from: Never on December 11, 2014, 07:55:35 pmtemporary serialize transactions providing a lock with an update These things sound good. I'm not that familiar with Firebird so do not know how to go about implementing it on the database. ---------------------------------------------------------------------- rasberryrabbit · 2014-12-12 04:00 More simple psql. Code: [Select]               Create Generator MyTest_GEN; ...                CREATE TRIGGER "MyTest_id_gen" FOR SAMPLE                ACTIVE BEFORE INSERT POSITION 0                AS BEGIN                IF (NEW.id IS NULL OR NEW.id = 0) THEN                NEW.id = GEN_ID(MyTest_GEN, 1);                END ---------------------------------------------------------------------- sin_dragan · 2014-12-12 13:09 @ Never Thx, just what I was looking for. In my case it's a single user application so it's not a problem. I was thinking about this problem in multi user environment, maybe the solution is to create a sequence that (in the moment of applying updates) reads max(field) and increment it by 1. ---------------------------------------------------------------------- Never · 2014-12-12 14:07 @sin_dragan glad you made it QuoteI was thinking about this problem in multi user environment,....imo a "botle nec" with update before adjusting the max will do just fine (do not forget there might be multithreading software but hds are not ....) there might be other more elegant solutions for this i will be happy to learn about them Edit*** QuoteThese things sound good. I'm not ......@goodname is not that complicated just call update on the record with the max setting the same value  for a field of your choice calling update will lock the record then proceed with the rest as usual ---------------------------------------------------------------------- goodname · 2014-12-12 14:37 Well @sin_dragan since your application is a single user this is not an issue. @Never my understanding is that calling update will lock records for write not read operations. This means that two transactions running in parallel can read the same max value. The lock would have to be on reads operations. Apparently transactions in Firebird are handled differently from most other database engines so I'm not sure how to get a read lock for that engine. ---------------------------------------------------------------------- Never · 2014-12-12 14:54 practice show that the suggested solution do not produce dublicates but if you want more power and a way to feel more secure there is the [ WITH LOCK ] clause you can read more about it here [ http://www.firebirdsql.org/refdocs/langrefupd21-notes-withlock.html ] Edit***: Ex: Code: [Select]SELECT MAX([field]) FROM [table].... WITH LOCK ............ ---------------------------------------------------------------------- goodname · 2014-12-12 18:35 Quote from: Never on December 12, 2014, 03:54:09 pmpractice show that the suggested solution do not produce dublicates Yes this would be a rare case but if it can happen then it will eventually happen. QuoteIf the WITH LOCK clause succeeds, ... prevent any other transaction from obtaining write accessSo according to the Firebird documentation WITH LOCK does not prevent read operations. Think that a understanding of transaction isolation levels is needed. It may be that the default isolation level prevents this possibility. http://www.firebirdsql.org/manual/isql-transactions.html ---------------------------------------------------------------------- Never · 2014-12-12 18:49 Suggested [ WITH LOCK] because of QuoteWhen an UPDATE statement tries to access a record that is locked by another transaction, it either raises an update conflict exception or waits for the locking transaction to finish, depending on TPB mode.providing that you use update before reading the max you will get what you want i can't see how i can help more on this ---------------------------------------------------------------------- goodname · 2014-12-12 20:19 QuoteWITH LOCK is not available: ... with the DISTINCT operator, a GROUP BY clause or any other aggregating operation; ... Max() is an aggregate so cannot be used with lock. This is a transaction isolation issue. Apparently TPB stands for Transaction Parameter Buffer. ---------------------------------------------------------------------- Never · 2014-12-12 21:36 you are right this Code: [Select]SELECT MAX([field]) FROM [table].... WITH LOCK ............in the same command is wrong Edit*** my contribution to this subject ends here the only way to ensure uniqness with transactions is to use the build in autoinc,sequences or whatever is named from the engin... these algorithms operate outside of the transaction isolation to ensure the values produced are unique the other way is to find your way to do it one way provided already and in order to brake it and make it fault you must run amazon, ebay or something like that...  well if you are in this category you are in trouble with max+1 inside a transaction