>I use Firebird 2.5 for Windows. >I need to create UPDATE TRIGGER for Table A, but UPDATE for the Table A for >row ID=x is > only possible if TIME column for ID<x is >not< [null]. For example I can > update Time >column for row ID=234 but it should be not possible to update Time column for >row ID=235. >How can I do it on the Firebird server (using eg. Trigger, functions or >procedures)? >TABLE A: >ID | TIME >========================================== >229 | 07.06.2013, 18:06:35.871 >230 | 07.06.2013, 16:42:52.941 >231 | 07.06.2013, 16:42:53.286 >233 | 07.06.2013, 16:42:53.607 >234 | [null] >235 | [null] >236 | [null] >------------------------------------------ >Thank you for any feedback or example >Kukiejko
You may use a CONSTRAINT, i.e. something like (don't use TIME as a field name): ALTER TABLE A ADD CONSTRAINT C_A_TIME CHECK ( new.MyTime is null or (select MyTime from test where id < new.id order by id desc rows 1) is not null ); If you really want this only to be checked upon UPDATE (i.e. that it can be set during INSERT, add 'or old.id is null' or something. Be aware, though, that whilst your idea to check a 'previous' record for values can work great in a single user scenario, it might not work with multiple users. Assume one transaction having inserted record 234 that has not been committed yet and one or more other transactions having inserted other records after that. Now, these other transactions cannot see record 234 yet (since it has not been committed) and when trying to find the latest record, they may find the one with id 233. Hence, id 235 may update the time field and when the transaction that inserted record 234 later commits, you will find that table A has a value for MyTime for ID 235, but not necessarily for ID 234. HTH, Set
