Looking at Triggers again. Run into similar problem from years ago (Delphi 6'ish). Currently using Interbase 2017 Dev edition with Rad Studio 10.2.3.
Want a unique primary key (eg IDno), with a trigger. When make a new record (client side), want it automatically to generate a new IDno type of thing server side.
- Primary Keys require not NULL, so it throws an error before can activate the trigger;
Any suggestions to get around this?
What used to do, was slap a 0 or negative against the IDno client side, and changed the trigger to <1 check before insert. Then the new record is given a new IDno, but also need to refresh so have the new IDno client side. If have multiple users using system, can use like their UserID as negative value, then should avoid any chance of clash of unique even if tries to process several new records at same instance.
But this seams messy to me, so presume is a better way.
Other approach thought of would to be pass all the field values from client side, asking server to make new record (insert) server side, where the trigger should activate and work? Still need to refresh client side. Its also a lot bulkier on the code and harder to maintain.
Create a BEFORE INSERT trigger. This will resolve your not null problem.
It sounds like you've used AFTER INSERT, which would definitely be a problem generating any sort of key.
Thanks again Quinn,
Am using a BEFORE INSERT trigger with Interbase, but it popping null value not allowed on IDno error client side before it gets to the trigger, well before the Interbase trigger can solve the problem.
Given the BEFORE INSERT trigger test run Interbase side, works fine when make a new record, assigning a new IDno as should.
By changing the IDno to like -1, client side, then this can progress (get additional description field entered) through as described above. When update to the server/Interbase the BEFORE INSERT trigger fires, assigning it a new IDno.
Using DBGrid client side, which data-aware. Seams its realizing primary key unique/not null, so when go to insert a new record client side, throwing the null value not allowed on the IDno if its not been assigned a value, (client side, before a trigger can fire Interbase/Server side).
Have added client side to the DataSource, UpdateData event to assign the IDno (REC field = IDno field, DataSource attached to sf table)
procedure TForm2.DSsfUpdateData(Sender: TObject);
if ((DSsf.DataSet.FieldByName('REC').AsInteger <1) and (DSsf.DataSet.FieldByName('REC').AsInteger <> -1)) then
DSsf.DataSet.FieldByName('REC').AsInteger := -1;
Simple check if IDno < 1 and not already assigned, assign -1 value. I know, could have done check if null, then assign.
This stops the error firing, but messy to use the UpdateData handler vs just the trigger. I know can write the UpdateData procedure tidier. When testing, I tend to write verbose so can see exactly where at.
Your help much appreciated.
You can also call GEN_ID in a stored procedure that returns the value from the generator in an output parameter to the client application. The client can then assign the value to the primary key and display it to the user when the client creates a new record but before it has been posted.
That was in 2002 article still listed under embarcadero (Were they still Borland back then?). Which is how I tackled the problem back about 2001 as ran into the same BEFORE INSERT Interbase trigger not activate when make a new record. But if a better way since, prefer to use the newer better way.
Saw about Rad Studio XE3 they had added the generator field to the IBDataSet, which worked around to automatically trigger for new IDno. But presume newer way not use the old IBDataSet for 3-tier Server/Client architecture.
Using GEN_ID() in a BEFORE INSERT trigger has long been documented in the InterBase documentation set. See: http://docwiki.embarcadero.com/InterBase/2017/en/GEN_ID(_)
Duplicated the trigger code. Added a new record client side (without assigning a value to the primary IDno field REC). As moved from the record (even before a beforepost can fire client side, before can activate before insert trigger in Interbase), popped error exactly same as above.
...with message 'Field 'REC' must have a value'. ie detects field requires not null - client side.
ie same problem as described above using a trigger as the error fires client side before the trigger can activate Interbase side. Same as it did back about 2001.
The Newer IBDataSet (newer than 2001'ish, Generator property wasn't there back then) does the IDno generation automatically with its Generator property. But all the examples appear to not use the IBDataSet as much more effort to make 3 tier architecture in it. Need to set so much more using this than the newer Datasnap ClientDataSet model. This partly as the automatic settings that used to be part of datamodule diagraming (linking the IBDataSets for all master-detail relationships, plus better automatic Insert/Update/Delete SQL, table fields), now have to be done more manually. Also IBDataSet is locking in using Interbase, so harder to change to another database if so choose, while ClientDataSet more flexible.
So I'll look to use the newer ClientDataSet (with DataSnap), calling a stored procedure (Interbase) to assign a new IDno at record creation.
It doesn't solve the problem, but steps around it. It avoids having to refresh to update the IDno field, as assigning the IDno directly, it not need a refresh. Can even simplify some, use a generic stored procedure to assign an IDno. eg if 100 tables requiring IDno's, no harm using the same stored procedure IDno generator (vs 1 per table) as not going to run out of numbers. As the IDno's internal, it not matter what they are, so long as they are unique. This basically how did it back 2001'ish, just moving to newer ClientDataSet/DataSnap model.
Thanks again Quinn.
Pointed me towards a result.