Firebird SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Firebird SQL Development
Reload this Page

Trigger, NEW and variable field name

Discuss Trigger, NEW and variable field name in the Firebird SQL Development forum on Dev Shed.
Trigger, NEW and variable field name Firebird SQL Development forum discussing administration, Firebird SQL syntax, or other Firebird SQL-related topics. Firebird is the evolution of Borland's Interbase product.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 15th, 2007, 08:33 AM
quipo quipo is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Posts: 60 quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 3 h 34 m 28 sec
Reputation Power: 11
Trigger, NEW and variable field name
Hi,

I'm writing a trigger and I need to access NEW.fieldname WHERE "fieldname" is a variable:

SQL Code:
Original - SQL Code 
  1. DECLARE VARIABLE fld VARCHAR(20);
  2. BEGIN
  3.   FOR
  4.     SELECT RDB$FIELD_NAME
  5.       FROM RDB$RELATION_FIELDS
  6.      WHERE RDB$RELATION_NAME='mytable'
  7.     INTO :fld
  8.   DO
  9.     INSERT INTO tblname (
  10.         ID, FIELD_NAME, FIELD_OLD, FIELD_NEW
  11.     ) VALUES (
  12.         GEN_ID(seq_name, 1),
  13.         :fld,
  14.         OLD.:fld,
  15.         NEW.:fld
  16.     );
  17. END;


but the "NEW.:fld" syntax isn't supported, and without the colon (NEW.fld) it tries to fetch the non-existing "fld" field.

How can I get the NEW and OLD values of the field?
Reply With Quote
  #2  
Old January 15th, 2007, 09:36 AM
pabloj's Avatar
pabloj pabloj is offline
Modding: Oracle MsSQL Firebird
Dev Shed God 6th Plane (7500 - 7999 posts)
  
Join Date: Jun 2001
Location: Outside US
Posts: 7,826 pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level)pabloj User rank is Captain (20000 - 30000 Reputation Level) 
Time spent in forums: 2 Months 3 Weeks 1 Day 9 h 59 m 53 sec
Reputation Power: 278
Can you please describe what you are trying to achieve?
Reply With Quote
  #3  
Old January 15th, 2007, 09:55 AM
quipo quipo is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Posts: 60 quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 3 h 34 m 28 sec
Reputation Power: 11
Quote:
Originally Posted by pabloj
Can you please describe what you are trying to achieve?


I'd like to explore a way to log the changes to each field (saving both the old and new value) for a given table with a trigger.

For instance, if I update/insert a row into TABLE1, I'd like to save the old and new values of each changed field, plus the field name and other properties, without having to list the table fields in the trigger. I can get them querying the system tables from within the trigger.

In the code above, the FOR cycle loops through the table field names and uses each one as a variable for the subsequent INSERT query.

This way, I can use the same trigger on any table, without knowing its structure beforehand.

As said, it's more like a proof of concept than an actual need, still I'd love to know if/how that's possible.
Reply With Quote
  #4  
Old January 16th, 2007, 02:21 AM
upscene upscene is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 223 upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 7 h 14 m 50 sec
Reputation Power: 8
You cannot access NEW<fields-by-name>. It cannot be done.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Reply With Quote
  #5  
Old January 16th, 2007, 03:08 AM
quipo quipo is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Posts: 60 quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level)quipo User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 1 Day 3 h 34 m 28 sec
Reputation Power: 11
Quote:
Originally Posted by upscene
You cannot access NEW<fields-by-name>. It cannot be done.

"NEW.fieldname" works just fine, it's "NEW.:varname" that doesn't work (I wanted to confirm that the latter was true).

For the records, I also got a reply by a borland employee, saying the same thing. Oh well... so much for my proof of concept
Reply With Quote
  #6  
Old January 16th, 2007, 03:19 AM
upscene upscene is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 223 upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 7 h 14 m 50 sec
Reputation Power: 8
Quote:
"NEW.:varname"


Yes, that's what I meant to say

my "field by name" should be taken as being a string variable of some kind.


Do note that Firebird is not equal to InterBase. Dynamic SQL in Firebird PSQL allows for much more (weird) constructs compared to InterBase.


Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Firebird SQL Development > Trigger, NEW and variable field name

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway.