| Subject | Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column |
|---|---|
| Author | Vishal Tiwari |
| Post date | 2017-08-09T09:57:36Z |
Hi Paul,
I am getting below error while executing the trigger you shared. May be because of new FB version? I am executing using FlamRobin tool.
Please see error below:
*** IBPP::SQLException ***
Context: Statement::Prepare( create trigger before update on Table_Name
as
begin
if (new.col3 is distinct from old.col3)
then new.col5 = current_timestamp )
Message: isc_dsql_prepare failed
SQL Message : -104
can't format message 13:896 -- message file C:\Windows\system32\firebird.msg not found
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 23
update
On Wednesday, 9 August 2017 3:15 PM, "Paul Vinkenoog paul@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
Hello Vishal,
Yes, a trigger is definitely the way to go, e.g. like this:
set term #;
create trigger before update on MyTable
as
begin
if (new.col3 is distinct from old.col3)
then new.col5 = current_timestamp;
end#
set term ;#
If col3 is non-nullable you can simply use "new.col3 <> old.col3" in the
test.
Mind you, an explicit update that re-enters the existing value in col3
won't cause col5 to be updated!
HTH,
Paul Vinkenoog
> I have one table say "MyTable", which has five columns, say Col1,
> Col2,...,Col5 and it has 10 rows. Col5 is of Timestamp.
>
> My issue is, whenever I am updating Col3, that time, only for that row
> of Col5, Timestamp value should be updated to the current timestamp.
>
> What would be the best option for this?
>
> If trigger is the best way then how would I do it? As I never worked
> on Triggers.
Yes, a trigger is definitely the way to go, e.g. like this:
set term #;
create trigger before update on MyTable
as
begin
if (new.col3 is distinct from old.col3)
then new.col5 = current_timestamp;
end#
set term ;#
If col3 is non-nullable you can simply use "new.col3 <> old.col3" in the
test.
Mind you, an explicit update that re-enters the existing value in col3
won't cause col5 to be updated!
HTH,
Paul Vinkenoog