Mirror a view to a table

I would like to replicate a single view to a table that is stored on another db server (connected as linked server object).
Is there a way to imitate the behavior of a trigger (insert, delete, update) for a view?
I could assign the triggers to the table that provides the primary key.
So I could handle insert, delete events.
But what about updates that affect row in other tables that are used in this view?

Code Snippet

CREATE TRIGGER mirror_tableA_insert
ON [TESTDB].[dbo].[tableA]
FOR INSERT
AS
BEGIN
set nocount on
SET XACT_ABORT ON
set REMOTE_PROC_TRANSACTIONS off
INSERT INTO OPENQUERY(TESTLINKED, 'SELECT * FROM tableA')

SELECT *
FROM [TESTDB].[dbo].[myView] orig
INNER JOIN inserted i
ON i.prim = orig.prim

END

Thanks in advance for any hints!

Marcus

Use replication service instead of using the Trigger.

|||

It is not totally clear what you are attempting to accomplish.

Why are you using OPENQUERY instead of a LinkedServer?

And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

|||

Arnie Rowland wrote:

It is not totally clear what you are attempting to accomplish.
Why are you using OPENQUERY instead of a LinkedServer?
And you may be better served by exploring an 'INSTEAD OF' TRIGGER.

I would like to replicate this view to a MySQL database that is used for a website.

Basically it's data synchronization job. So all dml statements that affect this view should trigger a procedure that synchronizes the MySQL table with the view on the SQLServer.

Yes I managed to set up a linked server for the MySQL DB through the MyODBC 3.51 driver.
First I tried to copy all the rows in this view to a MySQL table using a INSERT INTO OPENQUERY statement.

But a trigger can't be added to a view.

Manivannan.D.Sekaran wrote:

Use replication service instead of using the Trigger.

Yes the replication service would be the way to go when the target server (subscriber) would be a DB2 or Oracle database. But my target datatbase is MySQL (using it for a website).
Maybe it's possible to define this OLE DB data source as subscriber?

|||

You can't do directly, but there are some thrid party tools available. C-JDBC: Clustered JDBC is one of the tool.(http://c-jdbc.objectweb.org/)

|||

Marc Cicero wrote:

But a trigger can't be added to a view.

As I wrote earlier, you may wish to explore an INSTEAD OF TRIGGER.

An INSTEAD OF TRIGGER can work on a VIEW.