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

7 views
Skip to first unread message

David Carr

unread,
Jun 30, 2020, 1:09:11 AM6/30/20
to firebird-general
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.


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

unread,
Jun 30, 2020, 5:23:01 AM6/30/20
to firebird...@googlegroups.com
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

unread,
Jun 30, 2020, 1:32:38 PM6/30/20
to firebird...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages