Solved

linked server trigger question syntax

Posted on 2007-12-03
10
637 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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
 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

756 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