Thread View: borland.public.interbase.sql
9 messages
9 total messages
Started by Isaac
Wed, 21 May 2008 20:11
new and old values
Author: Isaac
Date: Wed, 21 May 2008 20:11
Date: Wed, 21 May 2008 20:11
11 lines
1113 bytes
1113 bytes
Hello, I am trying to make a scenario of saving a bill contains many items, I need when a user make an update to be able to get old and new values of that update so I am thinking of writing a stored procedure can I access new and old from the TIBStoredProc or it is only accessible from triggers ? please also advice if there is a better approach Thanks
Re: new and old values
Author: Martijn Tonies
Date: Wed, 21 May 2008 20:39
Date: Wed, 21 May 2008 20:39
26 lines
1738 bytes
1738 bytes
Hi, > I am trying to make a scenario of saving a bill contains many items, I > need when a user make an update to be able to get old and new values of > that update so I am thinking of writing a stored procedure > > can I access new and old from the TIBStoredProc or it is only accessible > from triggers ? NEW and OLD is only available in Triggers. That being said, a TIBStoredProc is a client-side component to call a server-side Stored Procedure, you cannot touch the internals of that procedure from the component. -- 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
Re: new and old values
Author: Isaac
Date: Wed, 21 May 2008 21:51
Date: Wed, 21 May 2008 21:51
21 lines
1683 bytes
1683 bytes
Martijn Tonies wrote: > Hi, > >> I am trying to make a scenario of saving a bill contains many items, I >> need when a user make an update to be able to get old and new values of >> that update so I am thinking of writing a stored procedure >> >> can I access new and old from the TIBStoredProc or it is only accessible >> from triggers ? > > NEW and OLD is only available in Triggers. > > That being said, a TIBStoredProc is a client-side component to call a > server-side Stored Procedure, you cannot touch the internals of that > procedure from the component. > > ok , what about starttransaction, commit, rollback can be affect the changes made by a stored procedure ? Thanks
Re: new and old values
Author: Isaac
Date: Wed, 21 May 2008 21:52
Date: Wed, 21 May 2008 21:52
1 lines
1075 bytes
1075 bytes
a stored procedure called from trigger not the TIBStoredProc component
Re: new and old values
Author: Bill Todd [TeamB]
Date: Thu, 22 May 2008 01:29
Date: Thu, 22 May 2008 01:29
12 lines
1443 bytes
1443 bytes
Isaac wrote: > ok , what about starttransaction, commit, rollback can be affect the > changes made by a stored procedure ? The changes made by a stored procedure are part of the context of the transaction that executes the stored procedure. If you roll the transaction back the changes made by the stored procedure will be rolled back. -- Bill Todd (TeamB)
Re: new and old values
Author: Isaac
Date: Thu, 22 May 2008 10:59
Date: Thu, 22 May 2008 10:59
8 lines
1264 bytes
1264 bytes
OK just to make sure: - In case of a trigger execute a stored procedure I can not commit or rollback changes from my app, right ? - In case I called the stored procedure from TIBStoredProc I can commit or rollback changes from my app, right ? Thanks
Re: new and old values
Author: Martijn Tonies
Date: Thu, 22 May 2008 13:25
Date: Thu, 22 May 2008 13:25
28 lines
1951 bytes
1951 bytes
> OK just to make sure: > - In case of a trigger execute a stored procedure I can not commit or > rollback changes from my app, right ? The operations that cause the trigger to fire run in a specific transaction context, if you commit/rollback the transaction, this goes for all operations in that context, so yes, this includes stored procedure changes. > - In case I called the stored procedure from TIBStoredProc I can commit > or rollback changes from my app, right ? See above. This is also covered in the InterBase documentation. -- 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
Re: new and old values
Author: Bill Todd [TeamB]
Date: Thu, 22 May 2008 14:47
Date: Thu, 22 May 2008 14:47
19 lines
1579 bytes
1579 bytes
Isaac wrote: > OK just to make sure: > - In case of a trigger execute a stored procedure I can not commit or > rollback changes from my app, right ? Wrong. You cannot do anything that will cause a trigger to fire without first starting a transaction. You cannot even read from the database without first starting a transaction. > > - In case I called the stored procedure from TIBStoredProc I can > commit or rollback changes from my app, right ? Right. -- Bill Todd (TeamB)
Re: new and old values
Author: Quinn Wildman (CodeGear Develope...
Date: Thu, 22 May 2008 17:10
Date: Thu, 22 May 2008 17:10
10 lines
1676 bytes
1676 bytes
Bill's 100% correct here, but I thought I'd add one comment. The comment is that the transaction that gets started when you first connect to a database is generally invisible to you. Most connectivity drivers just do it for you. The one exception I know of is IBX. Bill Todd [TeamB] wrote: > Wrong. You cannot do anything that will cause a trigger to fire without > first starting a transaction. You cannot even read from the database > without first starting a transaction.
Thread Navigation
This is a paginated view of messages in the thread with full content displayed inline.
Messages are displayed in chronological order, with the original post highlighted in green.
Use pagination controls to navigate through all messages in large threads.
Back to All Threads