Export data with an insert trigger to a linked server

Hi,

I have the following query I use to update an Informix database which is a linked server on my Sql server:
INSERT OPENQUERY(ProntoTestServer, 'Select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line from TestDatabase:Quotes0')
 select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier,                         tara_sub_line from test.dbo.quotes2

This query works, but I need to automate this process for every time a new record is added to the quotes2 table. How can I do this by using an insert trigger?
LVL 1
cErasmusAsked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you Enabled XA transactions in the MSDTC?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just create the necessary trigger and use the inserted table (is where the new rows are). Like this will work even for massive inserts:
 
CREATE TRIGGER trgInsertInformix ON dbo.quotes2  
AFTER INSERT 
AS
INSERT OPENQUERY(ProntoTestServer, 'Select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line from TestDatabase:Quotes0')
SELECT item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line 
FROM inserted
GO

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Try...

CREATE TRIGGER [dbo].[TriggerName] ON [dbo].[TableName] AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON
	
    IF EXISTS (SELECT TOP 1 1 FROM INSERTED)
    BEGIN
--Perform your operation here..

INSERT OPENQUERY(ProntoTestServer, 'Select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier, tara_sub_line from TestDatabase:Quotes0')  select item_description, tara_extended_descr, tara_supplier_item_code, tara_brand, tara_uom, tara_qty_ordered, tara_supplier,                        
 tara_sub_line from Inserted.

		
    END
	
END

Open in new window



Hope it helps !!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
cErasmusAuthor Commented:
Hi Guys,

Thanks for the assistance.

Vitor, I have created the trigger as per your solution, but once I import data into the quotes2 table it is not automatically triggered to export the data, is there somewhere I can check what error was caused by the trigger?
0
 
Pawan KumarDatabase ExpertCommented:
Insert a single row and then chk.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
but once I import data into the quotes2 table it is not automatically triggered to export the data
How did you import the data?
0
 
cErasmusAuthor Commented:
I have a SSIS package that imports the data
0
 
cErasmusAuthor Commented:
But in my final solution the quotes2 table's data will be generated from an excel spreadsheet....
0
 
Pawan KumarDatabase ExpertCommented:
Trigger works row by row . Insert one row manually and the try.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Trigger works row by row . Insert one row manually and the try.
That's not true.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I have a SSIS package that imports the data
That's using bcp or BULK INSERT command? Because if it does then triggers aren't fired during the process.
0
 
cErasmusAuthor Commented:
I have tested by inserting a row manually and I get this error:
MSDTC is not available on this server.

In SSIS I use a dataflow task with an OLE DB Source and a OLE DB Destination. Vidor, if you said "That's using bcp or BULK INSERT command". So for SSIS packages import of data will not fire triggers?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
No, a Data Flow Task does not use bulk insert. For that you have Bulk Insert Task.

MSDTC is not available on this server.
You'll need MSDTC since it's the component that allows you to run distributed transactions in remote servers.
0
 
cErasmusAuthor Commented:
Hi Vitor,

I have started the MSDTC service and I get the following error:

The operation could not be preformed because OLE DB provider "Ifxoledbc" for linked server "ProntoTest" was unable to begin a distributed transaction.
0
 
cErasmusAuthor Commented:
Hi Vitor, thanks that did the trick!
0
 
cErasmusAuthor Commented:
THanks for the help, you guys rock!
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I really do not agree with the points split since Pawan's comment was mostly a copy from mine first comment after he edited it when he saw what's missing (moderators can easily detect this). Also he was trying to get you to the wrong path. If I weren't here you'ld still around this issue.
Cheers
0
 
Pawan KumarDatabase ExpertCommented:
No points please for this. Not contributed enough.

@vitor - did not agree with you. My code is very different from you.
0
 
cErasmusAuthor Commented:
Sorry about this Vitor, can the moderators please award all points to Vitor Montalvão.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
My code is very different from you.
Only thing different is this useless part "IF EXISTS (SELECT TOP 1 1 FROM INSERTED)". It's useless because it's an insert trigger so at least a row will exist in INSERTED.

Sorry about this Vitor, can the moderators please award all points to Vitor Montalvão
Not really your fault but I'm tired to see questions that I've solved hijacked by the same guy. You can fill a request for a moderator in your main question by clicking in the three dots button (...).

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.