Home Forums SQL Server 2008 SQL Server 2008 - General Please HELP !! :SQL SERVER with MySQL Linked Server. Error Executing Trigger RE: Please HELP !! :SQL SERVER with MySQL Linked Server. Error Executing Trigger

  • First, let me say that your trigger will cause you problems as it does not cater for multiple row updates, this is something you should look into changing as soon as possible.

    Now, moving on to your immediate problem, this can be handled slightly differently.

    My recommendation to you is to not attempt to do cross system updates within a trigger.

    Better would be to write the updates to a local sql server database and process them outside of the transaction (the transaction that fired the trigger), this will remove the potential bottleneck every time you update your table.

    Something like this would be your table to hold details of updates required to your MySQL table:

    create table some_holding_table(inserted_time datetime default(getdate()) not null, ID int, Stock int);

    GO

    And your trigger would be something like this:

    ALTER TRIGGER [dbo].[TriggerStock]

    ON [dbo].[T_Contabilidad]

    FOR UPDATE,insert

    AS

    BEGIN

    INSERT some_holding_table(ID,Stock)

    SELECT Idt_Conta,Stock FROM INSERTED

    END

    GO

    Then you have a periodic process to read "some_holding_table" and perform the required updates to the remote system.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]