|
|
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
If you can't read my code, try converting it here[^]
|
|
|
|
|
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 f***ing 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.
|
|
|
|
|
Wait, that's actually allowed? In all my years I've never attempted to update the DB in a view. I mean who would?
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. It still confuses me, but at least it solves the problem at hand.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Oh... Emmm... Gee
Jeremy Falcon
|
|
|
|
|
Access?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Access?
No, SQL Server...haven't tried it in Access.
"Go forth into the source" - Neal Morse
|
|
|
|
|
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.
- 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>
|
|
|
|
|
But that's only if you issue an INSERT , UPDATE or DELETE statement against the view.
My interpretation of PIEBALDconsult's message is that he's only reading the view - a SELECT statement. And I can't think of any way that a SELECT statement 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.
|
|
|
|
|
An ETL in SSIS actually, but that's just details, if I say SELECT ... FROM someview that 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.
|
|
|
|
|
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.
I need to get rid of this cold. My brain is getting mushy.
|
|
|
|
|
Exactly. That's been covered.
|
|
|
|
|
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 usertable
And in the background, the GetManagerID function accesses LDAP and refills the usertable or something.
|
|
|
|
|
ISomething something = listOfSomethings.Where(...).OrderBy(...).FirstOrDefault();
if (something != null)
{
return something;
}
else
{
return null;
} Oh no, how come you thought you could return something when it is null ? That's a different null !
|
|
|
|
|
Saw similar in javascript last week:
function checkIfValid() {
var isValid = false;
if (doSomeOtherCheck()) {
isValid = true;
}
...
if (isValid == true) {
return true;
} else {
return false;
}
}
[Edit] Updated to show more content from the function.
Ah, I see you have the machine that goes ping. This is my favorite. You see we lease it back from the company we sold it to and that way it comes under the monthly current budget and not the capital account.
modified 31-Aug-21 21:01pm.
|
|
|
|
|
That makes sense if valid is "true" (string) or 1 (int), but you want to return a boolean
|
|
|
|
|
I should have wrote that further up the function, isValid is set to either true or false
Ah, I see you have the machine that goes ping. This is my favorite. You see we lease it back from the company we sold it to and that way it comes under the monthly current budget and not the capital account.
modified 31-Aug-21 21:01pm.
|
|
|
|
|
|
Using the "null" keyword in code should produce a compilation error. Not that it would break enything, right?
|
|
|
|