467,917 Members | 1,319 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,917 developers. It's quick & easy.

The inserted and deleted tables are empty ? (trigger operation)

Hello everyone,

i am trying to work with linked servers...

The local server is the one used by UPS worldwide software (it is Microsoft SQL Server Desktop Engine, v 8.00.2050). The linked server is 2005 MS server.

on update/insert event for some table i hooked up the trigger that connects to linked server and updates the record in some DB.

To test, i create a stored procedure that updates the record in UPS table that has that trigger. The table is updated, the trigger works, no updates happens if....

if use 'inserted' table to get the updated record, the fields are null. If i hardcode the parameters, i see them reflected in the linked server (the table on linked server is updated)... I DON'T UNDERSTAND, why the number of records in inserted is equal to 0... so naturally my parameters stay equal to NULL... here is the code:

STORED PROCEDURE:
AS
SET NOCOUNT ON
UPDATE upswsdb.dbo.calPackage
SET Sm_trackingNo = '11Z73X1A60343030610'
WHERE m_primaryKey = 2
RETURN



TRIGGER:
COMMIT TRANSACTION


DECLARE @TrackingNumber nvarchar(30)
DECLARE @TrackingOrderID nvarchar(35)


SET @TrackingNumber = (SELECT Sm_trackingNo FROM inserted)
SET @TrackingOrderID = (SELECT Sm_referenceText0 FROM inserted)

--SET @TrackingNumber = (SELECT COUNT(*) FROM inserted)
--SET @TrackingOrderID = (SELECT COUNT(*) FROM deleted)

PRINT @TrackingNumber
PRINT @TrackingOrderID

--SET XACT_ABORT ON
UPDATE RemoteServer.DB.dbo.OrderTable
SET ShippingTrackingNumber = @TrackingNumber
WHERE OrderId = @TrackingOrderID
BEGIN TRANSACTION

Thanks for any help to resolve it...

HF
Sep 17 '08 #1
  • viewed: 5305
Share:
2 Replies
ck9663
Expert 2GB
Try to interchange the COMMIT and the BEGIN TRANSACTION

-- CK
Sep 17 '08 #2
it did the trick. Thank you!
Sep 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Raphael Bauduin | last post: by
10 posts views Thread by MaRCeLO PeReiRA | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.