Solved

linked server trigger question syntax

Posted on 2007-12-03
10
602 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

708 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