creating a trigger that updates row in a linked mysql server

Question

i created a linked Mysql server on SQL server 2008 r2. i'm trying to create a trigger on sql table that automatically updates a field in the linked server table, i have a table called "QFORCHOICE" in sql that has fields "Prodcode,prodname and avqty" and a table "que_for_choie" in mysql that has fields "procode,proname and avqty" i want the trigger to update the value of "procode" in the linked server if the value of "prodcode" in sql server changes. this is what i have so far but it has errors,

 create trigger [QFORCHOICE]
 ON dbo.QFORCHOICE
  FOR INSERT
   AS
    DECLARE @prodcode numeric(18,0)
    DECLARE @prodname varchar(50)
    DECLARE @avqty numeric(18,0)

    BEGIN
        SELECT
        @procode = procode,
        @proname = proname,
        @avqty = avqty
        FROM inserted

        update [LINKED_MYSQL].[que_for_choice]
        SET prodname=@prodname,avqty=@avqty
        WHERE prodcode = @prodcode
    end

can anybody please help. thanks in advance

What errors are you getting? Also, looking at you query I think you missing someting on this line "update [LINKED_MYSQL].[que_for_choice]". Normally, you'll write [Linked_serverName].[DatabaseName].[Schema].[TableName] — Bayeni
– Bayeni, Commented Mar 27, 2015 at 9:46
the error message says "Invalid object name 'LINKED_MYSQL.Intelimenu.dbo.que_for_choice' . "LINKED_MYSQL"is the name of the linked server "Intelimenu is the Database name — Sefiso
– Sefiso, Commented Mar 27, 2015 at 10:07

maazza · Accepted Answer · 2017-01-16 11:24:30Z

1

1- From within a trigger, you shouldn't attempt to access anything external to the current database. It will severely slow down any insert activity, and if there are any networking issues or the remote server is down for any reason, you'll then cause the original transaction to roll back. This is rarely the right thing to do

2- you're making the reliability of your system dependent on the reliability of two servers rather than one (say they both have 99% reliability - your system that ties them together with a trigger now has 98% overall reliability).

edited Jan 16, 2017 at 11:24

maazza

7,26116 gold badges67 silver badges98 bronze badges

answered Jan 16, 2017 at 10:59

vijay joshi

382 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

creating a trigger that updates row in a linked mysql server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related