A database view that affects the underlying data?!
-
Wait, that's actually allowed? In all my years I've never attempted to update the DB in a view. I mean who would? :wtf:
Jeremy Falcon
Yep, I found this out a couple of weeks ago when faced with a challenging client who insists on keeping two systems (databases)...one receives data automatically as a daily scheduled task from their receiving and pos systems while the other holds the same data but in monthly summary, and after passing through the accounting dept/system. Looking for an easy way to keep 95% of the shared tables in synch, I decided to try recreating the shared tables as views in one of the databases. I was fully expecting an error when creating a new record in a view...but it did what it wasn't supposed to do. :confused: It still confuses me, but at least it solves the problem at hand. :laugh:
"Go forth into the source" - Neal Morse
-
Yep, I found this out a couple of weeks ago when faced with a challenging client who insists on keeping two systems (databases)...one receives data automatically as a daily scheduled task from their receiving and pos systems while the other holds the same data but in monthly summary, and after passing through the accounting dept/system. Looking for an easy way to keep 95% of the shared tables in synch, I decided to try recreating the shared tables as views in one of the databases. I was fully expecting an error when creating a new record in a view...but it did what it wasn't supposed to do. :confused: It still confuses me, but at least it solves the problem at hand. :laugh:
"Go forth into the source" - Neal Morse
Oh... Emmm... Gee
Jeremy Falcon
-
Yep, I found this out a couple of weeks ago when faced with a challenging client who insists on keeping two systems (databases)...one receives data automatically as a daily scheduled task from their receiving and pos systems while the other holds the same data but in monthly summary, and after passing through the accounting dept/system. Looking for an easy way to keep 95% of the shared tables in synch, I decided to try recreating the shared tables as views in one of the databases. I was fully expecting an error when creating a new record in a view...but it did what it wasn't supposed to do. :confused: It still confuses me, but at least it solves the problem at hand. :laugh:
"Go forth into the source" - Neal Morse
-
-
Yep, I found this out a couple of weeks ago when faced with a challenging client who insists on keeping two systems (databases)...one receives data automatically as a daily scheduled task from their receiving and pos systems while the other holds the same data but in monthly summary, and after passing through the accounting dept/system. Looking for an easy way to keep 95% of the shared tables in synch, I decided to try recreating the shared tables as views in one of the databases. I was fully expecting an error when creating a new record in a view...but it did what it wasn't supposed to do. :confused: It still confuses me, but at least it solves the problem at hand. :laugh:
"Go forth into the source" - Neal Morse
-
N_tro_P wrote:
A view can't actually change the data as it is or its not a "view" per say
..if the underlying data changes, and they request the results of the same view two weeks later? You expect the same results, or new ones?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
That depends on what's backing it. In many cases, the tables are "live" and you expect the latest data every time you read the view. In other cases, the tables are in a "reporting" database that might contain "the state of the data as of close-of-business yesterday" and you would not expect different data throughout the day. In that case, something would update the reporting database in the evening. But, it seems like somehow they got this view to perform that update! So they want others (me) to call it once in the evening, but not during the day. :sigh: I'd rather get the updated data, but others are reported as being "confused" by having the data change. :badger:
-
I think that's different (but still not a good idea) in this case I'm "just reading" from the view, but apparently something (a function or who knows what) is making changes.
I'm struggling to see how that would be possible. :confused:
- A view cannot insert, update, or delete any records. It can only select records.
- A view can select from a table-valued function, but a TVF cannot insert, update, or delete records. Neither can it call a stored procedure, or use temporary tables.
- You can't create a trigger that would fire when records are selected from a table or view. And you can't create triggers on TVFs at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I'm struggling to see how that would be possible. :confused:
- A view cannot insert, update, or delete any records. It can only select records.
- A view can select from a table-valued function, but a TVF cannot insert, update, or delete records. Neither can it call a stored procedure, or use temporary tables.
- You can't create a trigger that would fire when records are selected from a table or view. And you can't create triggers on TVFs at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Richard Deeming wrote:
A view cannot insert, update, or delete any records. It can only select records.
Actually it can under some circumstances
MSDN wrote:
You can modify the data of an underlying base table through a view, as long as the following conditions are true: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following: An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP. A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable. The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
CREATE VIEW (Transact-SQL) - MSDN[^] <edit>BTW, most databases support it since it's standardized in SQL-92</edit>
Wrong is evil and must be defeated. - Jeff Ello
-
Richard Deeming wrote:
A view cannot insert, update, or delete any records. It can only select records.
Actually it can under some circumstances
MSDN wrote:
You can modify the data of an underlying base table through a view, as long as the following conditions are true: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following: An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP. A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable. The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses. TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.
CREATE VIEW (Transact-SQL) - MSDN[^] <edit>BTW, most databases support it since it's standardized in SQL-92</edit>
Wrong is evil and must be defeated. - Jeff Ello
But that's only if you issue an
INSERT,UPDATEorDELETEstatement against the view. My interpretation of PIEBALDconsult's message is that he's only reading the view - aSELECTstatement. And I can't think of any way that aSELECTstatement against a view could modify the underlying data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
I'm struggling to see how that would be possible. :confused:
- A view cannot insert, update, or delete any records. It can only select records.
- A view can select from a table-valued function, but a TVF cannot insert, update, or delete records. Neither can it call a stored procedure, or use temporary tables.
- You can't create a trigger that would fire when records are selected from a table or view. And you can't create triggers on TVFs at all.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Yeah, I don't know either. If they're not smart enough to know not to do it, then they're probably not smart enough to know how to do it. I can imagine a CLR function that could pull data from somewhere, but that's just not a good idea. For example
SELECT id , name , GetManagerID(id) FROM usertableAnd in the background, the GetManagerID function accesses LDAP and refills the usertable or something. -
But that's only if you issue an
INSERT,UPDATEorDELETEstatement against the view. My interpretation of PIEBALDconsult's message is that he's only reading the view - aSELECTstatement. And I can't think of any way that aSELECTstatement against a view could modify the underlying data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I got the impression he's "reading" the view via an SP or similar.
Piebald wrote:
in this case I'm "just reading" from the view, but apparently something (a function or who knows what) is making changes.
Anyway, that means I misread what you wrote.
Wrong is evil and must be defeated. - Jeff Ello
-
But that's only if you issue an
INSERT,UPDATEorDELETEstatement against the view. My interpretation of PIEBALDconsult's message is that he's only reading the view - aSELECTstatement. And I can't think of any way that aSELECTstatement against a view could modify the underlying data.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Exactly. That's been covered.
-
I got the impression he's "reading" the view via an SP or similar.
Piebald wrote:
in this case I'm "just reading" from the view, but apparently something (a function or who knows what) is making changes.
Anyway, that means I misread what you wrote.
Wrong is evil and must be defeated. - Jeff Ello
An ETL in SSIS actually, but that's just details, if I say
SELECT ... FROM someviewthat should cause the data to change. -
An ETL in SSIS actually, but that's just details, if I say
SELECT ... FROM someviewthat should cause the data to change.Then the only possibility I can think of is if some idiot (IMHO) has created a trigger on the view.
Wrong is evil and must be defeated. - Jeff Ello
-
N_tro_P wrote:
A view can't actually change the data as it is or its not a "view" per say
..if the underlying data changes, and they request the results of the same view two weeks later? You expect the same results, or new ones?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
Eddy Vluggen wrote:
You expect the same results, or new ones?
As another said it depends. Not all vies are static and will report on new data rather than a slice. Depends on how the view was built, but that is not what the OP is really about. It sounds more like a view was causing the data to actually change meaning a query was doing adding to the data set which is against all view policies.
Computers have been intelligent for a long time now. It just so happens that the program writers are about as effective as a room full of monkeys trying to crank out a copy of Hamlet. The interesting thing about software is it can not reproduce, until it can.
-
Brisingr Aerowing wrote:
Whoever wrote that view needs to be drawn and quartered. Then each piece quartered again. Then those chunks dumped into a lava pit.It's called a VIEW for a reason, people!
I am thinking it isn't actually the developer of the view but the dev of the queries for the view. A view can't actually change the data as it is or its not a "view" per say, but any idiot could have put some insertion or anything with in a query that is accessed when a view is accessed. Granted, probably the same developer but not necessarily.
Computers have been intelligent for a long time now. It just so happens that the program writers are about as effective as a room full of monkeys trying to crank out a copy of Hamlet. The interesting thing about software is it can not reproduce, until it can.
Or even better: Some triggers.
The language is JavaScript. that of Mordor, which I will not utter here
This is Javascript. If you put big wheels and a racing stripe on a golf cart, it's still a fucking golf cart.
"I don't know, extraterrestrial?" "You mean like from space?" "No, from Canada." If software development were a circus, we would all be the clowns. -
Eddy Vluggen wrote:
You expect the same results, or new ones?
As another said it depends. Not all vies are static and will report on new data rather than a slice. Depends on how the view was built, but that is not what the OP is really about. It sounds more like a view was causing the data to actually change meaning a query was doing adding to the data set which is against all view policies.
Computers have been intelligent for a long time now. It just so happens that the program writers are about as effective as a room full of monkeys trying to crank out a copy of Hamlet. The interesting thing about software is it can not reproduce, until it can.
N_tro_P wrote:
It sounds more like a view was causing the data to actually change meaning a query was doing adding to the data set which is against all view policies.
A view does not have side-effects. Adding a row is not the same as a view with side-effects :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Then the only possibility I can think of is if some idiot (IMHO) has created a trigger on the view.
Wrong is evil and must be defeated. - Jeff Ello
SQL Server doesn't let you create a trigger that fires on a
SELECT.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
SQL Server doesn't let you create a trigger that fires on a
SELECT.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Quite right you are. :doh: I need to get rid of this cold. My brain is getting mushy.
Wrong is evil and must be defeated. - Jeff Ello
