asked on

linked server trigger question syntax

i am trying the following trigger statements and having problems. very new to this. can you help?
____
purpose. to send data inserted/updated in my table (which is called [Samples Received] (and field names are DNA, Kit,) to a table in a linked server called [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples where field names are DNANumber,KitID

ive tried these and my error is:Msg 156, Level 15, State 1, Procedure insertDataOnLinkedServer, Line 4
Incorrect syntax near the keyword 'from'.

CREATE TRIGGER insertDataOnLinkedServer ON [Samples Received]
FOR INSERT AS
INSERT INTO [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples SELECT dna, kit, from Inserted

also

CREATE TRIGGER TRIG_SamplesRecieved_copyToRemote ON [Samples Received]
FOR INSERT, UPDATE
AS
DELETE FROM [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples WHERE DNANumber IN (SELECT DNANumber FROM DELETED)
INSERT INTO [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples SELECT DNA, Kit, from Inserted

any help on what im doing wrong?

ASKER CERTIFIED SOLUTION

Ashish Patel

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nito8300

try this instead for the trigger:

CREATE TRIGGER insertDataOnLinkedServer ON [Samples Received]
FOR INSERT AS
INSERT [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples (DNA, KIT)
SELECT dna, kit
FROM INSERTED

and

CREATE TRIGGER TRIG_SamplesRecieved_copyToRemote ON [Samples Received]
FOR INSERT, UPDATE
AS
DELETE FROM [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples WHERE DNANumber IN (SELECT DNANumber FROM DELETED)
INSERT [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples (DNA, KIT)
SELECT DNA, Kit
FROM INSERTED

edi77

ASKER

i tried that and got this error:
Msg 8197, Level 16, State 4, Procedure TRIG_SamplesRecieved_copyToRemote, Line 1
Object 'Samples Received' does not exist or is invalid for this operation.

that is the name of MY table.

i am trying to insert into the celllab upon the change in MY inserted table in sampes received.

is it wrong the way i did it?

edi77

ASKER

my field names for my table are dna, and kit but for the celllab they are DNANumber and KitID

should i not be using the names for the labs fields in my trigger statement?

how does it know?

Goodangel Matope

Try this...

CREATE TRIGGER insertDataOnLinkedServer ON [Samples Received]
FOR INSERT AS
DECLARE @dna VARCHAR(255) --or whatever data type it should be
DECLARE @kit VARCHAR(255) --or whatever data type it should be

SELECT @dna = dna, @kit = kit from Inserted

INSERT INTO [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples VALUES (@dna, @kit)

Anthony Perkins

I would like to contribute but you have way too many open quesitons (28 out of 42 asked), so I think I will move on.

Here are all the questions that are now considered abandoned:
1 10/12/2007 500 Windows (kerberos) authenti... Open ASP.Net Programm... ...
2 10/11/2007 500 Creating a Pre Defined Quer... Open ASP.Net Programm... ...
3 10/11/2007 500 Insert on Gridview in ASP.NET Open ASP.Net Programm... ...
4 10/10/2007 500 Basic Questions about ASP2.... Open ASP.Net Programm...
5 10/09/2007 50 ASP. NET 2.0/Visual Studio ... Open ASP ...
6 09/18/2007 250 can I use the requried fiel... Open ASP.Net Programm... ...
7 09/02/2007 500 trouble with dos batch script Open MS DOS
8 08/28/2007 500 Batch programming in Windows Open MS DOS
9 07/25/2007 500 User permissions for someon... Open SQL Server 2005
10 07/19/2007 50 Need an easy way to print u... Open MS SQL Server
11 07/18/2007 50 Easy Visual Studio 2005 que... Open MS SQL Server ...
12 07/17/2007 500 Can I change the number of ... Open MS SQL Server
13 07/17/2007 500 SQL Server 2005 connection ... Open MS SQL Server
14 07/11/2007 500 SQL Server 2005 query builder Open MS SQL Server
15 07/06/2007 500 saving SQL Server 2005 files Open Databases ...
16 05/08/2007 500 making access query break d... Open MS Access ...
17 04/10/2007 500 Complex validation rule wit... Open MS Access
18 03/14/2007 500 Split forms in Access 2007 Open MS Access ...
19 03/03/2007 500 form load events and changi... Open MS Access

Thank you for your active participation to this community.

edi77

ASKER

sorry, i wont leave this one open if you can help please do.

some of them i never got a good resolution i think.

Anthony Perkins

Sorry, but that is not the way it works here.

Good luck.

edi77

ASKER

hmmm not sure what i did wrong but i started wrapping up some of them. didnt know it was such a problem for people. well if you hadnt said anything i wouldnt have really known so at least i can clean them up

thanks anyway.

edi77

ASKER

ive already cleared a few open questions , will anyone else help besides this guy who doesnt want to?