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.