May 9, 2019 at 1:20 pm
Hi all,
I have a pending upgrade to an application which changes a database table into a view. The issue I have is I have 4 copies of the same application on different servers in different regions and use the existing table to update a master table hosted locally using triggers. With this change I'm not able to use the same logic so I'm trying to work out how to create a live copy of the view data in a duplicate table stored in my regional admin databases which I can then use as before. I have no access to the underlying tables the view will be using due to the sensitivity of the new data these tables will be holding. Unfortunately the data needs to be updated as soon as changes are seen in the view so I cannot use a scheduled batch process and we're talking about a few million rows in each region. Does anyone know of a way to populate a live copy of view data in a destination table?
Thanks in advance
May 9, 2019 at 1:43 pm
How did the data previously get from the remote databases into your locally hosted 'existing' table?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2019 at 1:46 pm
Hi Phil,
I used insert and delete triggers on the existing tables to manage the data. Unfortunately I do not believe this is possible on a view.
May 9, 2019 at 1:48 pm
Hi Phil, I used insert and delete triggers on the existing tables to manage the data. Unfortunately I do not believe this is possible on a view.
OK, is part of the new requirement that these triggers be removed? What is driving the need for change?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2019 at 2:12 pm
no, I have tried to add new triggers but can only add instead of triggers. unless there's something I'm missing. They have changed and locked down the new source tables as they included financial information to these in the new update. Which is fine if you only run one system however we need to manage who has access to these claims globally, but have no interest in the financials. We need the security information hosted in these views in a global audit table, hence the need to collate the information from 4 different systems. Hopefulyl this makes sense.
May 9, 2019 at 2:16 pm
If I can replicate the view into a table I could work with that but with only access to the view and i'm having trouble implementing this idea. I've tried triggers with no luck, Stored Procs would need to run on a schedule where I need live up to date data so I'm running out of ideas to be honest. There's probably something simple I'm missing.
May 9, 2019 at 2:28 pm
I don't think you are missing anything simple.
Are you able to revisit the 'data must be live' requirement? If it transpires that a 10-minute latency period is acceptable, it potentially opens up the problem to scheduled 'runs every x minutes' type tasks (this, of course, depends on how much data we're talking about and how fast your queries run).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 9, 2019 at 2:38 pm
Phew, was beginning to think I was loosing the plot.
I think that's the only option to be honest. I'll start working through that option and see if it's viable for them.
Thank you for your time Phil, very much appreciated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply