By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

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

P: 3
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
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Try to interchange the COMMIT and the BEGIN TRANSACTION

-- CK
Sep 17 '08 #2

P: 3
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.