August 20, 2010 at 6:01 am
Hello everyone,
I'm confused as to the replication work flow. How would the following task be accomplished with replication?
Scenario:
I want to create a view from replicated base tables. I was thinking that I could create a trigger on the view such that when changed data is replicated over to the base tables from the source system, the trigger on the view will fire and run a custom sql statement.
I'm not sure if you can have a trigger on a view as I am suggesting. Instead, would I need to create a physical table with the results of my view. And if so, how would the change detection process work?
Scenario1:
Source System --> Replicated Base Tables and View --> Trigger on View is Fired --> Custom SQL Statements Executed?
Scenario2:
Source System --> Replicated Base Tables and View --> Create/Insert Into Physical Table from Replicated View --> Trigger on Table is Fired --> Custom SQL Statements Executed?
I have read many articles that give me bits and pieces of information, but I need a little guidance on this particular situation. Any help would be greatly appreciated.
August 20, 2010 at 6:12 am
Letron,
I do not know about views, but:
I currently use transactional replication to replicate data from numerous locations to a central location. The data comes from day tables, which are cleared every night. In the central location where our DW is, we have a trigger which then moves this data to various locations. The trigger is solely on insert. That is the only thing that we care about. At the end of the day, these day tables are cleared, which is then replicated to the central table, clearing the data for that particular location (location is part of the PK). This does not remove the data from the DW.
Works well - if there is a network loss between the various locations and the central repository, once the connection is restored the replication picks up where it left off.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply