June 4, 2009 at 4:38 am
Hi,
i have a ERP application that updates orders, projects, workunits etc.
I have inserted a "after update" (insert, delete) trigger into the tables of the applications DB that writes changes to the main applications database into my own DB.
Now i get a big problem:
The main-application apparently receives the "first column of the last changed row" and uses this returned ID as the identifier for the last changed dataset.
However, when i activate my triggers, the last ID from my Status-Changes table is returned instead of the ID of the inserted row from the application.
Example without triggers:
Application inserts an order with unknown DB-ID.
-> gets back the database ID from SQL-Server and then inserts other stuff for this order-ID.
If my trigger is active the following seems to happen:
Application inserts an order with unknown DB-ID.
---> my trigger writes the order-ID as given from the database to my temp-table
---> Application now get the ID of the temp-table row that was inserted!!!
How can i avoid this?
My triggers are really simple:
ALTER TRIGGER [dbo].[RPS_OPERATION_DELETED]
ON [dbo].[OPERATION_D]
AFTER DELETE
AS
--insert into dbo.StatusChanges (StatusName, ID, Aenderungsdatum, neuerStatus, inZDBuebernommen)
--select 'ZeusAGStamm', I_OPERATIONID, GETDATE(), 'deleted', '0'
--from DELETED
insert into dbo.StatusChanges (StatusName, ID, Aenderungsdatum, neuerStatus, inZDBuebernommen, str_FK1, str_FK2, str_FK3)
select 'ZeusAGStamm', DELETED.I_OPERATIONID, GETDATE(), 'deleted', '0', 'AGStamm', DELETED.I_OPERATIONID, MACHINE_D.S_PATTERNNR
from DELETED
inner join MACHINE_D on DELETED.I_MACHINEID = MACHINE_D.I_MACHINEID
Is there a way to NOT make SQL-Server do any changes to the "last inserted" values inside my trigger?
btw: the trigger itself works without errors....
THX
Markus
June 4, 2009 at 4:58 am
Addition:
changing the database where the "status_changes" table lies (my temp table) does not change the problem 🙁
CU
Markus
June 4, 2009 at 8:37 am
Have a look at Books OnLine for @@IDENTITY because it sounds like you main application is using that builtin function to get the last value inserted into an identity column. If the insert fires a trigger that inserts into another table with an identity column, then @@IDENTITY returns that second value.
This will be the value from your temp table if you are inserting something there.
June 4, 2009 at 10:48 am
I would agree with twillcomp. The application si probably using @@IDENTITY. I'd drop the identity column from the status changes table.
Does adding a trigger to the application's database invalidate your support agreement?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 4, 2009 at 12:19 pm
THX for the answers.
I will check if removing my own ID column solves the problem.
I can sort by the change-date then.
As to the support: what support??? 🙂
I guess usually it is not appreciated to add triggers to foreign databases. But for our application it is pretty much essential to do so. We even work together with the other application developers company. It is just very hard to get support from the programers themselfes and a new version takes months to appear 🙁
THX again,
Markus
June 5, 2009 at 7:03 am
We have triggers that update audit tables. In order to avoid the problem your are describing we do not use an identity column in the audit table. Instead we use an integer primary key column and we generate the unique integer ourselves. Then, if the calling application uses @@identity it does not get the wrong value.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
June 6, 2009 at 8:50 am
Just wanted to give feedback:
removing the IDENTITY column from my table proved successfull.
🙂
THX again,
Markus
June 6, 2009 at 11:22 pm
Thanks for providing the feedback. You ought to talk to the vendor about using @@Identity when using SCOPE_IDENTITY() or the OUTPUT clause will eliminate this problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply