How to detect which fields set through command in BEFORE UPDATE trigger?

[David Carr]

Hi there,

Thanks firstly to those responsible for porting over the Groups to Google Groups. I hope this will have some longevity.

So I was under the impression, wrongly it seems, that within a BEFORE UPDATE trigger that I could do the following:

IF (NEW.<field> IS NULL) THEN

   ...<do some action>

where if <field> is not provided in the SQL command, that it would be NULL. But it seems I am wrong here, where <field> simply contains the value that already exists in the record if not given in the query.

I suspect I made the wrong assumption by following the way legacy code was done using the same syntax above on a BEFORE INSERT trigger, where <field> IS null.

I have read these articles:
http://www.firebirdfaq.org/faq133/
https://firebirdsql.org/manual/nullguide-testing.html#nullguide-testing-change

however with regards to the second link, what I want to do here is not test for change, but rather test if the SQL provided in the query/command specifies this parameter. Is there a [easy] way to do this?

To give context, I am setting timestamps if particular fields have changed, and I want to perform this in triggers - so that it is always, and reliably, done - as opposed to the code explicitly setting the value.

Thanks in advance,

David

[Dimitry Sibiryakov]

30.06.2020 05:23, David Carr wrote:
> what *I want to do here is not test for change,* but rather test if the SQL provided in the
>
> To give context, *I am setting timestamps if particular fields have changed,* and I want to

You should decide what you really want.

No, there is no way to identify fields set by user query.

--
WBR, SD.

[Mark Rotteveel]

On 30-06-2020 05:23, David Carr wrote:
> So I was under the impression, wrongly it seems, that within a BEFORE
> UPDATE trigger that I could do the following:
>
> IF (NEW.<field> IS NULL) THEN
>    ...<do some action>
>
> where if <field> is not provided in the SQL command, that it would be
> NULL. But it seems I am wrong here, where <field> simply contains the
> value that already exists in the record if not given in the query.
>
> I suspect I made the wrong assumption by following the way legacy code
> was done using the same syntax above on a BEFORE INSERT trigger, where
> <field> IS null.
>
> I have read these articles:
> http://www.firebirdfaq.org/faq133/
> https://firebirdsql.org/manual/nullguide-testing.html#nullguide-testing-change
>
> however with regards to the second link, what I want to do here is not
> test for change, but rather test if the SQL provided in the
> query/command specifies this parameter. Is there a [easy] way to do this?
>
> To give context, I am setting timestamps if particular fields have
> changed, and I want to perform this in triggers - so that it is always,
> and reliably, done - as opposed to the code explicitly setting the value.

There is no option to do that. You can only detect that the new version
has been changed from the old value by using NEW.column is distinct from
OLD.column. You cannot detect whether the value was explicit set through
the query.

Mark
--
Mark Rotteveel