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

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old August 13th, 2003, 07:21 AM
badukist badukist is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 62 badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 3 m 18 sec
Reputation Power: 7
Send a message via Yahoo to badukist
Insert to another table in trigger code

I want to log changes from a table to a separate log table. I tried using triggers but the code that insert values from current updated table to the log table don't work, the server return "Column does not belong to referenced table" error.
It is possible?

Last edited by badukist : August 13th, 2003 at 08:29 AM.

Reply With Quote
  #2  
Old August 13th, 2003, 11:30 AM
goreXP's Avatar
goreXP goreXP is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Location: Bucharest
Posts: 72 goreXP User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 10 m 10 sec
Reputation Power: 6
Send a message via MSN to goreXP Send a message via Yahoo to goreXP
create a stored procedure and call it from trigger, it could answer to problem.

Reply With Quote
  #3  
Old August 14th, 2003, 01:55 AM
badukist badukist is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 62 badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 3 m 18 sec
Reputation Power: 7
Send a message via Yahoo to badukist
It works, thanks a lot.
And is even better, I can call the same procedure from insert, update and delete trigger.
Thanks again.

Reply With Quote
  #4  
Old August 27th, 2003, 08:16 AM
skidder skidder is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Aug 2003
Posts: 15 skidder User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to skidder
Do not understand your problem. I can access whatever from triggers in my code. And why don't you use universal triggers (same trigger for insert, update and/or delete) ?

Reply With Quote
  #5  
Old August 28th, 2003, 04:36 AM
badukist badukist is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 62 badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 3 m 18 sec
Reputation Power: 7
Send a message via Yahoo to badukist
The truth is that I am newbie in SP and triggers language.
I tried to log changes to a separate table with the same structure and 2 extra columns , rowid (the PK) and transaction_type, which can be I,U or D

The code :
INSERT INTO MY_TABLE_LOG (transaction_type , some_field) VALUES ('I', new.some_field) didn't worked in Insert trigger, gave me "Column doesn't belong to referenced table" error message.

If I use universal triggers, how can I know which operation is done (I,U or D) ?

Last edited by badukist : August 28th, 2003 at 04:43 AM.

Reply With Quote
  #6  
Old August 28th, 2003, 05:58 AM
skidder skidder is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Aug 2003
Posts: 15 skidder User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Send a message via ICQ to skidder
the code you have written should work. Just tried:

create table my_table (
some_field integer
);
create table my_table_log (
transaction_type char(1),
some_field integer
);
commit;
create trigger my_table_insert for my_table before insert as
begin
insert into my_table_log(transaction_type, some_field)
values('I', new.some_field);
end;
commit;

and it worked (tested with Firebird 1.5 RC5 on Linux).
When you declare universal triggers you can check INSERTING/UPDATING/DELETING boolean pseudo-variables to determine action. Here is a simple example:

create or alter trigger my_table_all for my_table before insert or update or delete as
begin
insert into my_table_log(transaction_type, some_field) values(
case
when inserting then 'I'
when updating then 'U'
else 'D'
end,
case
when inserting or updating then new.some_field
else old.some_field
end);
end

Reply With Quote
  #7  
Old August 28th, 2003, 07:19 AM
badukist badukist is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Jun 2003
Posts: 62 badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level)badukist User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 13 h 3 m 18 sec
Reputation Power: 7
Send a message via Yahoo to badukist
Thanks , I must check again.
I used IBExpert , maybe the way it format keywords/names has to do with the error message.

Seems that I have a lot to learn.

Last edited by badukist : August 28th, 2003 at 07:30 AM.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesFirebird SQL Development > Insert to another table in trigger code


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
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 6 hosted by Hostway