April 11, 2008 at 4:49 am
Is there anyway to build a SQL Update that can test the fields during the update and not update them if they are the same, but still update fields that have changed? Is there a common teqnique to do this?
I'm thinking something along the lines of:
UPDATE Table1 SET recordtime=@rtime, foo=@foo, bar=@bar WHERE DATEDIFF(second, recordtime, @rtime) > 0 OR foo <> @Foo OR bar <> @bar
However, that statement will update all 3 if any of them are different.
hi
your update statement seems right. can you tell in more detail whether i am right. You want to update columns which is changed . no the columns are not known . it may be any column in the table
April 11, 2008 at 7:32 am
Nobody Real (4/7/2008)
Thanks. I am aware of how to write the trigger, that's not my problem. As I said, I cannot change the existing trigger, so i'm looking for a way to get around the problem in my update code.
Sounds to me that the trick is to set up this trigger to check the update operation....before the update operation occurs. since you can't "fix" or change the AFTER UPDATE trigger, then perhaps you need to put code like GSquared is looking at in the BEFORE UPDATE (also known as the INSTEAD OF) trigger.
Playing with another AFTER UPDATE trigger spells trouble to me since you would get into recursive trigger calls, etc.....
Still - looks awfully messy either way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 8:17 am
I don't think there is much sense putting such code into trigger, as it unnecessarily complicates logic. It's the duty of client to send a correct update/insert/delete statement containing only modified columns.
In Delphi it's simple. In VB or any other language/tool it should be too.
April 17, 2008 at 2:29 am
Jacob Luebbers (4/9/2008)
The same is true (though to a lesser extent) with any of the algorithms supported by HashBytes(). There is no guarantee that two of your rows that have the same HashBytes() value are the same - it's just that the chances of it happening are very small, especially with the newer algorithms with longer hash lengths like the SHA family. You're not relying on the hash to provide guarantee that the row hasn't changed though are you? Eg you're only using it to minimise the amount of rows that need to be manually compared column-to-column... so a hash collision is not dangerous to your validation process, rather simply causes a more thorough check to be done, right?Regards,
Jacob
Hi Jacob,
yes, we do some additional checks, until now hashbytes never failed. But unfortunately you are right, there is always the possibility to collisions, but until now this never happened here.
Best Regards,
Jan
April 17, 2008 at 2:47 am
UPDATETable1
SETrecordtime = case when DATEDIFF(second, recordtime, @rtime) > 0 then @rtime else recordtime end,
foo = case when @Foo <> foo then @Foo else foo end,
bar = case when @bar <> bar then @bar else bar end,
WHEREDATEDIFF(second, recordtime, @rtime) > 0
OR foo <> @Foo
OR bar <> @bar
N 56°04'39.16"
E 12°55'05.25"
April 18, 2008 at 2:06 pm
Peso (4/17/2008)
UPDATETable1
SETrecordtime = case when DATEDIFF(second, recordtime, @rtime) > 0 then @rtime else recordtime end,
foo = case when @Foo <> foo then @Foo else foo end,
bar = case when @bar <> bar then @bar else bar end,
WHEREDATEDIFF(second, recordtime, @rtime) > 0
OR foo <> @Foo
OR bar <> @bar
That's an interesting solution, i'll have to look into that.
It certainly makes the query monstrously large and difficult to maintain, but I might be able to live with that 😉
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply