Solved

linked server trigger question syntax

Posted on 2007-12-03
10
614 Views
Last Modified: 2012-05-05
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?
0
Comment
Question by:edi77
10 Comments
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 500 total points
ID: 20397001
There is one extra comma after Kit (between Kit and From) remove that and try both triggers. Like this.

CREATE TRIGGER insertDataOnLinkedServer ON [Samples Received]
 FOR INSERT AS
  INSERT INTO [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples 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 INTO [IN-MMGE-CELLLAB].TestDNACR.test.DNASamples SELECT DNA, Kit  from Inserted
0
 
LVL 9

Expert Comment

by:nito8300
ID: 20397050
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
0
 

Author Comment

by:edi77
ID: 20397062
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?
0
 

Author Comment

by:edi77
ID: 20397081
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?
0
 
LVL 11

Expert Comment

by:Goodangel Matope
ID: 20397301
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)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20397312
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.
0
 

Author Comment

by:edi77
ID: 20397327
sorry, i wont leave this one open if you can help please do.

some of them i never got a good resolution i think.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 20397394
Sorry, but that is not the way it works here.

Good luck.
0
 

Author Comment

by:edi77
ID: 20397533
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.
0
 

Author Comment

by:edi77
ID: 20397704
ive already cleared a few open questions , will anyone else help besides this guy who doesnt want to?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now