Having trouble with correct T-SQL syntax for linked server trigger 1

[vbajock]

This is the first trigger I've ever created, so bear with me....

First off, I created a linked server called "LDOS" .
In the parent server that hosts the server link, I am trying to create an inserted record trigger that will insert values from the inserted record, into a table located on this linked server called "tblProjectNumber". So far, I have this:


CREATE TRIGGER ProjectNoTrigger
ON dbo.tblArHistHeader
FOR INSERT

/* INSERT, UPDATE, DELETE



*/


AS

DECLARE @TransId VARCHAR(8)
DECLARE @CustId VARCHAR(10)
BEGIN
SELECT @TransId =(SELECT TransId FROM INSERTED);
END


BEGIN
SELECT @CustId =(SELECT CustId FROM INSERTED);
END

OPENQUERY (LDOS,"INSERT INTO LDOS.tblProjectnumbers (TransID, CustId)
VALUES(@TransId,@CustId)"

GO

It errors out on the OPENQUERY line with an error "Incorrect syntax near OPENQUERY"

Any help is appreciated!
Thanks!

 

[gmmastros]

CREATE TRIGGER ProjectNoTrigger
ON dbo.tblArHistHeader
FOR INSERT 

/* INSERT, UPDATE, DELETE 



*/


AS
    
INSERT 
INTO   LDOS.[!]database_name[/!].dbo.tblProjectnumbers (TransID, CustId)
Select TransId, CustId
From   Inserted

It looks like you may be making things harder than they have to be. There is a rather significant problem with the way you wrote your trigger. Whenever you write a trigger, you should write it as though there could be multiple rows inserted, deleted, or updated simultaneously.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Alan0568]

>>OPENQUERY (LDOS,"INSERT INTO LDOS.tblProjectnumbers (TransID, >>CustId)VALUES(@TransId,@CustId)"

as a starter this needs a tidy. should be
OPENQUERY (server ,'query')

so single not double quotes in the correct place and the number of brackets are wrong also.

 

[vbajock]

Thanks! Works like a charm!