February 20, 2012 at 1:36 pm
I want to be able to put a trigger on a view to update a table in another database. I have been supplied a view of a table from a third party database I do not have access to. I wish to put a trigger on this view to update a table in another third party application database table.
I want the trigger to execute once a change has occurred in the view. I have read about INSTEAD OF but not sure this can be used as I don't want to update the data in the view, just update data in the table once the data changes in the view.
Any advice on how to achieve this would be appreciated.
February 20, 2012 at 2:14 pm
A view is not the data...it is...well a view of the data. You don't change the data in a view, you change the data in a table.
It seems there is a separation between what you are describing and what you really want to do. Perhaps you can explain with some example tables and what you want to happen?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2012 at 2:38 pm
I have a view of a number of tables in a database (DB1) that gives the following fields for e.g.
Code,GroupID, Name, Location
The results of this view is then used to populate similar column's in a table in another database (DB2).
ID, Code, GroupID, Name, Location, VatNo
Note I do not have access to DB1 only the supplied view.
I want to change the values in the table for DB2 whenever a change occurs in the view from DB1. Normally I would just put a trigger on the table from DB1, but I cannot do this as all I have access to is a view.
Hope this makes it clear.
February 20, 2012 at 2:42 pm
Gotcha, the problem is that there is only an event on the table. Like I said, the view doesn't change, the base table is what changes. You got me stumped on this one...I will roll this around and see if I can think of anything. In the meantime we can see if somebody else will drop by with a stroke of genius...that happens rather frequently around here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 20, 2012 at 3:08 pm
What is the time requirement for this data to be updated in DB2? Can you schedule a job to query the view and then hve that job do whatever you need to do? (MERGE, UPDATE, DELETE, INSERT, whatever...)
Jared
CE - Microsoft
February 20, 2012 at 4:43 pm
...
February 21, 2012 at 2:10 am
Yea I was thinking of doing a job to populate the table from the view but was hoping there might be a cleaner way as the time interval needs to be close to instant.
February 21, 2012 at 5:41 am
Let's see... How many rows are in the table and how many get changed/inserted/updated over a 5 minute period, 2 minute, 30 sec, 10 sec? You could essentially run the job every 10 seconds if you can get it to run fast enough.
Jared
CE - Microsoft
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply