Convert Insert to Update inside a trigger

[marsss]

Hello, wounder if anyone have already try it.

I have some software doing insert in a table and it can t be change to make an update.

I would catch these insert within a trigger on my table, and turn this insert into a simple update. But I can t find how to deal with rollback transaction to cancel actual insert at same time doing my new update inside my trigger

Say my table look like this :
ID Value
1 10
2 20
3 30


Command being send :
INSERT INTO table (ID,Value)
SELECT 1, 33

Catch in trigger
CREATE TRIGGER [myTrigger] ON [dbo].

FOR INSERT
AS

Update myTable
set myTable.value = ins.value
from table myTable, inserted ins
where myTable.ID = ins.ID

<<Insert here whatever rollback transaction to cancel any insert done>>


Final result:
ID Value
1 33
2 20
3 30

 

[bborissov]

Change the trigger to be INSTEAD OF:

CREATE TRIGGER [myTrigger] ON [dbo].[table]
INSTEAD OF INSERT
AS
Update myTable
set myTable.value = ins.value
from table myTable, inserted ins
where myTable.ID = ins.ID

BUT read carefully what says BOL for INSTEAD OF triggers.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[marsss]

Great!

That is exactly what I was looking for

Thx you

 

[ESquared]

Another option is to use two tables, one for the inserted records and one for the "real" records.

It's simpler because your trigger can be a regular insert trigger instead of an "instead of insert" trigger, and it has the added benefit of making the insert table act like a history table. This can be invaluable when it comes to debug or problem time: you have a forensic trail, if you will, to know what the outside source gave you and then compare it to what your system is doing with it internally.