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.
