Hi *,

if I set in designer for PK column that it uses generator, the insert query generated then is

INSERT INTO "GENERATOR_TEST" ("FOO", "PK") VALUES (@p1, GEN_ID("GENERATOR_TEST_PK", 1)) RETURNING "PK"

And that's correct. But in Firebird often the trigger is used to generate the PK value. After some research I found

FirebirdTriggerSequences

value to put into app.config. If I set it to true, the command generated is

INSERT INTO "GENERATOR_TEST" ("FOO", "PK") VALUES (@p1, GEN_ID("GENERATOR_TEST_PK", 0)) RETURNING "PK"

Which is completely wrong. The

GEN_ID("GENERATOR_TEST_PK", 0)

will get the last value from sequence effectively resulting in some value being set. The trigger might be then confused. Especially when something like

new.pk = coalesce(new.pk, gen(GENERATOR_TEST_PK, 1)); 

is used (and this is pretty common in Firebird (sometimes also 0 checking)).

I think the query should completely omit the PK column or put there a NULL (which is what the engine is going to put there anyway).

Attached is simple raw app that shows the behavior.

Version: 4.0.13.407

As you're more familiar with firebird than me, what should be generated in the case of trigger sequences? Because we never heard complaints about this (and I do know it's used).

So in short: instead of the GEN_ID(<sequencename>, 0), what should be generated if trigger sequences is set to true?

As I said. Either omit the column or put there a NULL. So

INSERT INTO "GENERATOR_TEST" ("FOO", "PK") VALUES (@p1, NULL) RETURNING "PK"

or

INSERT INTO "GENERATOR_TEST" ("FOO") VALUES (@p1) RETURNING "PK"

A couple of things:

1: It's not 'completely wrong', just in cases when the trigger gets confused. Don't make it sound like it's the world's biggest problem. I mean, I can also say: just fix your trigger and move on. There's no need to be condescending. It annoys me to no end. It's likely not your intention so just a FYI

2: you didn't explain what should be done exactly so that's why I asked. I don't want to change things if I don't know what I'm doing, hence the question.

3: passing null, what will happen in the case of after triggers? inserting NULL then fails, but the way we do it now succeeds. The last thing I want is a breaking change. I don't know if Firebird has after triggers, but I guess they have (not sure, I can't find precise docs for Firebird)

(edit) the debate which triggered us to make the change to emit the sequences as you see them: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16585

What I'm after is a solution which always works, with any trigger type used for inserting sequence values. As I can't foresee what people do within their triggers, I have to guess. A before-insert trigger will work with the setup we have now, as it simply inserts the value generated in the trigger.

Otis wrote:

1: It's not 'completely wrong', just in cases when the trigger gets confused.

Sure, the query is correct. It's just because almost nobody always overwrites the value in insert trigger if there is some (aka not null or <>0). In that case it fails next time (because the value is some value (the one the generator is currently at until somebody bumps the generator). Also you cannot detect that somebody is inserting row with real value (which you might want to let go as is, untouched) or the value comes from reading the generator (not incrementing it).

Otis wrote:

It annoys me to no end.

That was not my intention. Sorry.

Otis wrote:

3: passing null, what will happen in the case of after triggers? inserting NULL then fails, but the way we do it now succeeds. The last thing I want is a breaking change. I don't know if Firebird has after triggers, but I guess they have (not sure, I can't find precise docs for Firebird)

Yes, it will fail. But I think it's then developer's fault. As I understand the "FirebirdTriggerSequences" config is that you're telling LLBLGen Pro "hey this column is generated from sequence and BTW I'm generating the value in trigger". Hence the RETURNING clause and hence it's expected to do something in BEFORE INSERT trigger. But my feeling about the config might be wrong.

Ok.

The setting is meant to avoid reading a new sequence value for a sequenced PK field. So what the trigger does is not important, all the setting does is use the existing sequence value instead of a new one.

This setting is required, as the value created by the trigger has to be returned, and the engine doesn't know that it should do that if the field isn't marked as a field which is sequenced.

So to recap, the problem comes down to: the value passed as the value for the sequenced field in the insert query can make triggers not produce the proper value in some cases. I agree that passing GEN_ID(sequence, 0) is indeed perhaps a bit odd, as the value produced by it isn't the right one anyway (as it's the value the sequence is on currently).

The order is (correct me if I'm wrong, please): - INSERT statement is formulated, fields get the value to insert - BEFORE INSERT trigger is executed, it can overwrite the values set to insert - Row is actually inserted into table. - AFTER INSERT trigger runs. - Output parameters are returned.

So a BEFORE INSERT trigger will overwrite the value in normal occasions, with the new value for a given sequence. In the current situation, the value for the field the trigger sees is always > 0 and is basically the sequence's current value.

I can't see how things will fail if I pass NULL indeed: the sequenced field is expected to get a new value in the trigger (otherwise the setting should be off). AFTER insert triggers will only work if there's a valid row inserted, and will never overwrite the value with a new sequence value, as the row has already been inserted (and this is only possible with a valid value)

I'll make the change to pass NULL instead of GEN_ID(sequence, 0), as it makes triggers work which will now fail, but won't make currently working triggers fail, as they overwrite the value currently already.

See attached DQE. This should solve your problem with sequences.

Confirmed. Works. Thanks.

good! The DQE I gave you is a release build, so you can keep on using that. It's automatically enrolled in the next build.

Hi, I have described the same problem as in the thread

The test project is working with version 4.0. with version 4.1, the same error comes again.

Best Regards

Christof

please be more specific, as Jiri started this thread, so we don't know whether you have exactly the same problem or not, and whether you have switched firebird compatibility levels for example.

Also be sure to use the latest 4.1 build, as we had a bug in the DQE for several databases in the RTM build.

I have a big project with a firebird database. Since version 4.1 no primary keys are more filled with the sequence generator. I came across the test project here. I have loaded the project and inserted the modified SD.LLBLGen.Pro.DQE.Firebird.dll. The test project works. Now I have reference all sd.llblgen * from version 4.1. The test project does not generate automatic primary keys. I use the version 4.1 of 14 november 2013

Please elaborate more on the following:

christof wrote:

I came across the test project here. I have loaded the project and inserted the modified SD.LLBLGen.Pro.DQE.Firebird.dll. The test project works. Now I have reference all sd.llblgen * from version 4.1. The test project does not generate automatic primary keys.

Be more specific in the steps you took to reproduce the issue. It seems like you are working on a former v4.0 project. What did you do to convert it? Did you regenerate code? What is the generated SQL?, etc. Thanks

christof wrote:

I have a big project with a firebird database. Since version 4.1 no primary keys are more filled with the sequence generator. I came across the test project here. I have loaded the project and inserted the modified SD.LLBLGen.Pro.DQE.Firebird.dll. The test project works. Now I have reference all sd.llblgen * from version 4.1. The test project does not generate automatic primary keys. I use the version 4.1 of 14 november 2013

Like I said: use the latest build because a bug related to this was fixed. I didn't say that for nothing . http://www.llblgen.com/pages/ChangeLogBrowser.aspx

You can download the latest build from the customer area.

Like I said: use the latest build because a bug related to this was fixed. I didn't say that for nothing Regular Smiley. http://www.llblgen.com/pages/ChangeLogBrowser.aspx

Sorry my mistake. Now it works. Thanks for the help

I wish you a nice weekend Christof

christof wrote:

Like I said: use the latest build because a bug related to this was fixed. I didn't say that for nothing Regular Smiley. http://www.llblgen.com/pages/ChangeLogBrowser.aspx

Sorry my mistake. Now it works. Thanks for the help

I wish you a nice weekend Christof

you too!