> I would like to delete records if a determined count of record is reached. > One trigger counts a generator for the primery key of the table named a. on > before insert. One trigger after insert counts a second generator like this: > ist=GEN_ID(TILOG_ALLG_ID_DEL, 1); > if(ist >= 30) then > begin > ist = gen_id(TILOG_ALLG_ID_DEL, -10); > ist = new.id -20; > delete from tilog_allg where id<=:ist; > end > end > > Some time it works, some time not! There were records inserted, but the > generator tilog_allg_id_del will not count every time and then the records > will not be deleted. > > I like to delete the oldest records. > > What can it be? I have also test it with read values from a table instead of > the static 30, 20, 10, of course with the same result. The generator will > not be count every time and then the records still present.
Generators are outside transaction control, so TILOG_ALLG_ID_DEL will be updated whether the original transaction commits or rolls back. One reason for a commit not happening, could be that one of the records that you try to update is being modified by another transaction. To rule out this possibility, I'd recommend you to modify the trigger to INSERT into a testtable rather than DELETE. If the testtable is updated correctly, then the reason for your problem is likely to be such a transaction issue (and there is no reasonable way to force a DELETE if there are concurrent transactions updating the record you want to delete). The solution could be as simple as making sure UPDATEs happened to a separate table, but of course, if the INSERTs take place in long-running transactions so that they might not even be committed by the time you try to delete them, then all records would still not be deleted. HTH, Set
