Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

linked server trigger question syntax

Posted on 2007-12-03
10
Medium Priority
?
655 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 1000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

719 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