Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Paul Vinkenoog p...@vinkenoog.nl [firebird-support]]

Hi Vishal,

> I would also like to know that if I need to learn Triggers and Stored
> Procedures then what document should I refer, I am am new for triggers
> and SP. Googling is random way I see. Would you refer any good books
> for this?

You'll find lots of free documentation under

   http://www.firebirdsql.org/en/documentation/

More specifically, here's the Language Reference:

   
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25.html

with the chapter on triggers:

   
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-trgr.html

and on Procedural SQL (the language you use inside triggers and Stored 
Procedures):

   
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql.html

The Firebird FAQ is also very useful:

   http://www.firebirdfaq.org/

And Helen Borrie's Firebird Book is a great manual which also includes 
chapter(s) on SP's, triggers and PSQL. It's not free though! You'll find 
it here:

   https://www.ibphoenix.com/

(You may need to scroll down a bit.)

Hope this helps!

Cheers,
Paul Vinkenoog

Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

Hi Paul,
You Rocks !!!n Yes It is working as expected.
I would also like to know that if I need to learn Triggers and Stored 
Procedures then what document should I refer, I am am new for triggers and SP. 
Googling is random way I see. Would you refer any good books for this?
Corrected Trigger is as below:
SET TERM ^ ;CREATE TRIGGER MYTRIGGER FOR TABLE_NAME ACTIVE
BEFORE UPDATE POSITION 0
as
begin
if (new.col3 is distinct from old.col3)
then new.col5 = current_timestamp;
end^
SET TERM ; ^
 

Thanking You And With Best Regards.
Vishal

On Wednesday, 9 August 2017 3:35 PM, "Paul Vinkenoog p...@vinkenoog.nl 
[firebird-support]"  wrote:
 

     Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] schreef op 
09-08-2017 11:58:
> I have put table name as Table_Name, which is actual table name.
> 
> On Wednesday, 9 August 2017 3:27 PM, Vishal Tiwari
>  wrote:
> 
> 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

My fault! If forgot the trigger name. It should be:

create trigger MyTrigger before update on Table_Name ...

Instead of MyTrigger, you choose a more meaningful name of course.

Cheers,
Paul Vinkenoog

  #yiv8541066980 #yiv8541066980 -- #yiv8541066980ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv8541066980 
#yiv8541066980ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv8541066980 
#yiv8541066980ygrp-mkp #yiv8541066980hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv8541066980 #yiv8541066980ygrp-mkp #yiv8541066980ads 
{margin-bottom:10px;}#yiv8541066980 #yiv8541066980ygrp-mkp .yiv8541066980ad 
{padding:0 0;}#yiv8541066980 #yiv8541066980ygrp-mkp .yiv8541066980ad p 
{margin:0;}#yiv8541066980 #yiv8541066980ygrp-mkp .yiv8541066980ad a 
{color:#ff;text-decoration:none;}#yiv8541066980 #yiv8541066980ygrp-sponsor 
#yiv8541066980ygrp-lc {font-family:Arial;}#yiv8541066980 
#yiv8541066980ygrp-sponsor #yiv8541066980ygrp-lc #yiv8541066980hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv8541066980 
#yiv8541066980ygrp-sponsor #yiv8541066980ygrp-lc .yiv8541066980ad 
{margin-bottom:10px;padding:0 0;}#yiv8541066980 #yiv8541066980actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv8541066980 
#yiv8541066980activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv8541066980
 #yiv8541066980activity span {font-weight:700;}#yiv8541066980 
#yiv8541066980activity span:first-child 
{text-transform:uppercase;}#yiv8541066980 #yiv8541066980activity span a 
{color:#5085b6;text-decoration:none;}#yiv8541066980 #yiv8541066980activity span 
span {color:#ff7900;}#yiv8541066980 #yiv8541066980activity span 
.yiv8541066980underline {text-decoration:underline;}#yiv8541066980 
.yiv8541066980attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv8541066980 .yiv8541066980attach div a 
{text-decoration:none;}#yiv8541066980 .yiv8541066980attach img 
{border:none;padding-right:5px;}#yiv8541066980 .yiv8541066980attach label 
{display:block;margin-bottom:5px;}#yiv8541066980 .yiv8541066980attach label a 
{text-decoration:none;}#yiv8541066980 blockquote {margin:0 0 0 
4px;}#yiv8541066980 .yiv8541066980bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv8541066980 
.yiv8541066980bold a {text-decoration:none;}#yiv8541066980 dd.yiv8541066980last 
p a {font-family:Verdana;font-weight:700;}#yiv8541066980 dd.yiv8541066980last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv8541066980 
dd.yiv8541066980last p span.yiv8541066980yshortcuts 
{margin-right:0;}#yiv8541066980 div.yiv8541066980attach-table div div a 
{text-decoration:none;}#yiv8541066980 div.yiv8541066980attach-table 
{width:400px;}#yiv8541066980 div.yiv8541066980file-title a, #yiv8541066980 
div.yiv8541066980file-title a:active, #yiv8541066980 
div.yiv8541066980file-title a:hover, #yiv8541066980 div.yiv8541066980file-title 
a:visited {text-decoration:none;}#yiv8541066980 div.yiv8541066980photo-title a, 
#yiv8541066980 div.yiv8541066980photo-title a:active, #yiv8541066980 
div.yiv8541066980photo-title a:hover, #yiv8541066980 
div.yiv8541066980photo-title a:visited {text-decoration:none;}#yiv8541066980 
div#yiv8541066980ygrp-mlmsg #yiv8541066980ygrp-msg p a 
span.yiv8541066980yshortcuts 

Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Paul Vinkenoog p...@vinkenoog.nl [firebird-support]]

Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] schreef op 
09-08-2017 11:58:
> I have put table name as Table_Name, which is actual table name.
> 
>  On Wednesday, 9 August 2017 3:27 PM, Vishal Tiwari
>  wrote:
> 
> 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

My fault! If forgot the trigger name. It should be:

   create trigger MyTrigger before update on Table_Name ...

Instead of MyTrigger, you choose a more meaningful name of course.


Cheers,
Paul Vinkenoog

Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

I have put table name as Table_Name, which is actual table name. 

On Wednesday, 9 August 2017 3:27 PM, Vishal Tiwari 
 wrote:
 

 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_Nameasbeginif (new.col3 is distinct from old.col3)then new.col5 
= current_timestamp )Message: isc_dsql_prepare failed
SQL Message : -104can't format message 13:896 -- message file 
C:\Windows\system32\firebird.msg not found
Engine Code    : 335544569Engine Message :Dynamic SQL ErrorSQL error code = 
-104Token unknown - line 1, column 23update
 

On Wednesday, 9 August 2017 3:15 PM, "Paul Vinkenoog p...@vinkenoog.nl 
[firebird-support]"  wrote:
 

     Hello Vishal,

> 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

  #yiv2712576062 -- #yiv2712576062ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv2712576062 
#yiv2712576062ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv2712576062 
#yiv2712576062ygrp-mkp #yiv2712576062hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv2712576062 #yiv2712576062ygrp-mkp #yiv2712576062ads 
{margin-bottom:10px;}#yiv2712576062 #yiv2712576062ygrp-mkp .yiv2712576062ad 
{padding:0 0;}#yiv2712576062 #yiv2712576062ygrp-mkp .yiv2712576062ad p 
{margin:0;}#yiv2712576062 #yiv2712576062ygrp-mkp .yiv2712576062ad a 
{color:#ff;text-decoration:none;}#yiv2712576062 #yiv2712576062ygrp-sponsor 
#yiv2712576062ygrp-lc {font-family:Arial;}#yiv2712576062 
#yiv2712576062ygrp-sponsor #yiv2712576062ygrp-lc #yiv2712576062hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv2712576062 
#yiv2712576062ygrp-sponsor #yiv2712576062ygrp-lc .yiv2712576062ad 
{margin-bottom:10px;padding:0 0;}#yiv2712576062 #yiv2712576062actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv2712576062 
#yiv2712576062activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv2712576062
 #yiv2712576062activity span {font-weight:700;}#yiv2712576062 
#yiv2712576062activity span:first-child 
{text-transform:uppercase;}#yiv2712576062 #yiv2712576062activity span a 
{color:#5085b6;text-decoration:none;}#yiv2712576062 #yiv2712576062activity span 
span {color:#ff7900;}#yiv2712576062 #yiv2712576062activity span 
.yiv2712576062underline {text-decoration:underline;}#yiv2712576062 
.yiv2712576062attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv2712576062 .yiv2712576062attach div a 
{text-decoration:none;}#yiv2712576062 .yiv2712576062attach img 
{border:none;padding-right:5px;}#yiv2712576062 .yiv2712576062attach label 
{display:block;margin-bottom:5px;}#yiv2712576062 .yiv2712576062attach label a 
{text-decoration:none;}#yiv2712576062 blockquote {margin:0 0 0 
4px;}#yiv2712576062 .yiv2712576062bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv2712576062 
.yiv2712576062bold a {text-decoration:none;}#yiv2712576062 dd.yiv2712576062last 
p a {font-family:Verdana;font-weight:700;}#yiv2712576062 dd.yiv2712576062last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv2712576062 
dd.yiv2712576062last p span.yiv2712576062yshortcuts 
{margin-right:0;}#yiv2712576062 div.yiv2712576062attach-table div div a 
{text-decoration:none;}#yiv2712576062 div.yiv2712576062attach-table 
{width:400px;}#yiv2712576062 div.yiv2712576062file-title a, #yiv2712576062 
div.yiv2712576062file-title a:active, #yiv2712576062 
div.yiv2712576062file-title a:hover, #yiv2712576062 div.yiv2712576062file-title 
a:visited {text-decoration:none;}#yiv2712576062 div.yiv2712576062photo-title a, 
#yiv2712576062 div.yiv2712576062photo-title a:active, #yiv2712576062 
div.yiv2712576062photo-title a:hover, #yiv2712576062 
div.yiv2712576062photo-title a:visited {text-decoration:none;}#yiv2712576062 
div#yiv2712576062ygrp-mlmsg #yiv2712576062ygrp-msg p a 
span.yiv2712576062yshortcuts 

Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

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_Nameasbeginif (new.col3 is distinct from old.col3)then new.col5 
= current_timestamp )Message: isc_dsql_prepare failed
SQL Message : -104can't format message 13:896 -- message file 
C:\Windows\system32\firebird.msg not found
Engine Code    : 335544569Engine Message :Dynamic SQL ErrorSQL error code = 
-104Token unknown - line 1, column 23update
 

On Wednesday, 9 August 2017 3:15 PM, "Paul Vinkenoog p...@vinkenoog.nl 
[firebird-support]"  wrote:
 

     Hello Vishal,

> 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

  #yiv6715055271 #yiv6715055271 -- #yiv6715055271ygrp-mkp {border:1px solid 
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv6715055271 
#yiv6715055271ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv6715055271 
#yiv6715055271ygrp-mkp #yiv6715055271hd 
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 
0;}#yiv6715055271 #yiv6715055271ygrp-mkp #yiv6715055271ads 
{margin-bottom:10px;}#yiv6715055271 #yiv6715055271ygrp-mkp .yiv6715055271ad 
{padding:0 0;}#yiv6715055271 #yiv6715055271ygrp-mkp .yiv6715055271ad p 
{margin:0;}#yiv6715055271 #yiv6715055271ygrp-mkp .yiv6715055271ad a 
{color:#ff;text-decoration:none;}#yiv6715055271 #yiv6715055271ygrp-sponsor 
#yiv6715055271ygrp-lc {font-family:Arial;}#yiv6715055271 
#yiv6715055271ygrp-sponsor #yiv6715055271ygrp-lc #yiv6715055271hd {margin:10px 
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv6715055271 
#yiv6715055271ygrp-sponsor #yiv6715055271ygrp-lc .yiv6715055271ad 
{margin-bottom:10px;padding:0 0;}#yiv6715055271 #yiv6715055271actions 
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv6715055271 
#yiv6715055271activity 
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv6715055271
 #yiv6715055271activity span {font-weight:700;}#yiv6715055271 
#yiv6715055271activity span:first-child 
{text-transform:uppercase;}#yiv6715055271 #yiv6715055271activity span a 
{color:#5085b6;text-decoration:none;}#yiv6715055271 #yiv6715055271activity span 
span {color:#ff7900;}#yiv6715055271 #yiv6715055271activity span 
.yiv6715055271underline {text-decoration:underline;}#yiv6715055271 
.yiv6715055271attach 
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 
0;width:400px;}#yiv6715055271 .yiv6715055271attach div a 
{text-decoration:none;}#yiv6715055271 .yiv6715055271attach img 
{border:none;padding-right:5px;}#yiv6715055271 .yiv6715055271attach label 
{display:block;margin-bottom:5px;}#yiv6715055271 .yiv6715055271attach label a 
{text-decoration:none;}#yiv6715055271 blockquote {margin:0 0 0 
4px;}#yiv6715055271 .yiv6715055271bold 
{font-family:Arial;font-size:13px;font-weight:700;}#yiv6715055271 
.yiv6715055271bold a {text-decoration:none;}#yiv6715055271 dd.yiv6715055271last 
p a {font-family:Verdana;font-weight:700;}#yiv6715055271 dd.yiv6715055271last p 
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv6715055271 
dd.yiv6715055271last p span.yiv6715055271yshortcuts 
{margin-right:0;}#yiv6715055271 div.yiv6715055271attach-table div div a 
{text-decoration:none;}#yiv6715055271 div.yiv6715055271attach-table 
{width:400px;}#yiv6715055271 div.yiv6715055271file-title a, #yiv6715055271 
div.yiv6715055271file-title a:active, #yiv6715055271 
div.yiv6715055271file-title a:hover, #yiv6715055271 div.yiv6715055271file-title 
a:visited {text-decoration:none;}#yiv6715055271 div.yiv6715055271photo-title a, 
#yiv6715055271 div.yiv6715055271photo-title a:active, #yiv6715055271 
div.yiv6715055271photo-title a:hover, #yiv6715055271 
div.yiv6715055271photo-title a:visited {text-decoration:none;}#yiv6715055271 
div#yiv6715055271ygrp-mlmsg #yiv6715055271ygrp-msg p a 
span.yiv6715055271yshortcuts 
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv6715055271 
.yiv6715055271green {color:#628c2a;}#yiv6715055271 .yiv6715055271MsoNormal 
{margin:0 0 0 0;}#yiv6715055271 o {font-size:0;}#yiv6715055271 

Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Paul Vinkenoog p...@vinkenoog.nl [firebird-support]]

Hello Vishal,

> 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

[firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column

[Vishal Tiwari vishuals...@yahoo.co.in [firebird-support]]

The timestamp should be updated in Col5 only when Col3 is updated and not for 
any other column update.

On Wednesday, 9 August 2017 2:20 PM, Vishal Tiwari 
 wrote:
 

 Hi All,
I have one issue to share with you.
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.

Kindly let me know if my issue is not clear.
Thanks In Advance.

With Best Regards.
Vishal