Current database error creating triggers

• ben-829332

  SSC Veteran

  Points: 256

  More actions

  July 28, 2008 at 4:47 am

  #196242

  I'm trying to create triggers to update a linked MySQL database using the DSN my_mysql. My code is:

  create trigger items_insert on test2.dbo.names

  for insert

  as

  set xact_abort on

  insert into openquery(my_mysql, 'select * from names')

  select id, name, age from inserted

  go

  create trigger items_update on test2.dbo.names

  for update

  as

  set xact_abort on

  delete from openquery(my_mysql, 'select * from names')

  where id in (select id from deleted)

  insert into openquery(my_mysql, 'select * from names')

  select id, name, age from inserted

  go

  create trigger items_delete on test2.dbo.names

  for delete

  as

  set xact_abort on

  delete from openquery(my_mysql, 'select * from names')

  where id in (select id from deleted)

  go

  This produces the error:

  Cannot create trigger on 'test2.dbo.names' as the target is not in the current database.

  Can anyone explain this?

• Ian Scarlett

  SSC-Insane

  Points: 23197

  More actions

  July 28, 2008 at 5:00 am

  #849070

  Cannot create trigger on 'test2.dbo.names' as the target is not in the current database.

  You need to be in the context of your test2 database when you execute the create trigger statement.

  You could put this at the top of your script:-

  use test2

  go

• ben-829332

  SSC Veteran

  Points: 256

  More actions

  July 28, 2008 at 5:13 am

  #849076

  Ok thanks, now I get the error:

  The requested operation could not be performed because the OLE DB provider ‘MSDASQL’ does not support the required transaction interface

  I've heard that this can be fixed in the provider options for MSDASQL, but I can't find these options anywhere in SQL Server Express. Are these features missing from the express version?

  How can I set it to accept transacted updates?