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

  • This is not a simple problem, but mister.magoo is correct. The trigger way is not likely to work out well. First a few words on why the trigger you have does not work at all:

    SELECT @ID = Idt_Conta,@stock=Stock FROM INSERTED

    This is the first error. A trigger fires once per statement, and a INSERT or UPDATE statement could affect multiple rows. You are only catering for one of them.

    UPDATE OPENQUERY(MYSQLVINC,'SELECT Idt_web, disponible FROM mibasedatos.t_web

    where idt_web=@id')

    This is the second error. The SELECT statement you pass to OPENQUERY is executed in MySQL where @id is likely to be a syntax error. It certainly has no relation to your local variable @id. To get the value into the query you would need to use dynamic SQL. But as already noted, @id should not be there at all, since you need to handle multiple-row operations.

    This could work in theory:

    UPDATE MYSQLVINC..mibasedatos.t_web

    SET disponible = CASE WHEN i.Stock > 0 THEN 1 ELSE 0 END

    FROM MYSQLVINC..mibasedatos.t_web m

    JOIN inserted i ON m.idt_web = i.Idt_conta

    But I would be surprised that it actually does in practice.

    As Mister.magoo suggests you need to find an asynchronous solution. The trigger could popoulate a queue table which you poll reguarly. You could also implement Change Tracking (which means that SQL Server maintains that queue table for you.)

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]