|
Do you guys know of any easy way to have one column in a table of which the value auto updates every time an UPDATE statement is called for a specific row?
Either one of the following two scenarios would be fine but I'm not sure what the easiest way would be of implementing it:
1. Let's say there's a column in the table called 'Version' (int). Every time an UPDATE statement is called, the value of the 'Version' column is automatically incremented by 1 for every row affected.
2. Let's say there's a column in the table called 'Modified' (datetime). Every time an UPDATE statement is called, the value of the 'Modified' column is automatically set to the current date and time. I have a similar concept to this in my DB with a column named 'Inserted' which has a default value of GetDate(). So for every INSERT into the table, the 'Inserted' column will automatically get the current date and time but I'd like to have something like this for UPDATE as well.
Any ideas? My best idea so far would be to create a trigger on the table, something as follows:
CREATE TRIGGER update_mytable
ON MyTable
FOR UPDATE
AS
BEGIN
UPDATE MyTable
SET Modified = GETDATE()
WHERE RecordID IN (SELECT RecordID FROM INSERTED)
END
But I'm hoping that there might be a more elegant solution.
|
|
|
|
|
This is an elegant solution, and is definitely your best bet here. Note that you should do an = test, not an IN test.
|
|
|
|
|
Thanks. But why would you say that I should use an = test as opposed to an IN test? What if the UPDATE statement affects more than one row? Is there any specific disadvantage to usign an IN test that I should consider?
|
|
|
|
|
I think that's the best approach. It should work for all updates. Second way I can think of is to create procedure for updating the table and using it for all updates, that occur. And inside this procedure you can also update your version and modified columns. But that required you to stick with the procedures for all updates.
Don't forget to rate answer, that helped you. It will allow other people find their answers faster.
|
|
|
|
|
Looks to be a good solution in my opinion.
Just consider using an INNER JOIN instead of IN
UPDATE mytable<br />
Set Modified = GetDate() From myTable A Inner Join Inserted B<br />
Where A.RecordId = B.RecordId
|
|
|
|
|
Thanks. Would you mind explaining the advantage of an INNER JOIN over an IN?
|
|
|
|
|
Sub queries are run in a temporary memory space, hence they can be slower.
However, most engines now are capable of converting sub queries into INs, so in general for new databases, you should be ok with both.
|
|
|
|
|
One thing to bear in mind: GETDATE isn't particularly accurate at the millisecond level. Although the datetime datatype is accurate to about 3 milliseconds, GETDATE does not give you that level of precision, as far as I can gather from a quick google it is somewhere around 15ms although things like machine load can affect it. If you are expecting to do repeated updates to the same row within a very short time period, this may not give you unique values for your Modified column. If your rows will not be updated that frequently, then it's not a problem.
I thought SQL Server supported the ability to automatically timestamp rows when they were updated. The timestamp is just a meaningless number but at least you can check whether a row is the version you expect it to be, and you can also tell the order in which updates were applied.
(I'm assuming SQL Server since you haven't said what database you are using.)
|
|
|
|
|
Hi all,
I have been using this code project to create custom taskbar notification windows. This worked perfectly well in Windows XP, but in Windows 7 the background goes black (instead of transparent) and the custom image gets smeared (as opposed to being clearly re-drawn as it "rises" from the taskbar). Have any of you run into this issue? If so, is there a simple workaround? If there isn't, I would not be adverse to learning how to create these on my own (without someone else's code), but I'm not entirely sure where to start. Graphics and drawing aren't exactly my strongest suits.
Thank you for your time.
-Anfai
|
|
|
|
|
If you got the code from an article, then there is a "new message" button at the bottom of that article, which causes an email to be sent to the author. They are then alerted that you wish to speak to them.
Posting this here relies on them "dropping by" and realising it is for them.
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
Ah, I understand. I wasn't intending my question to be solely for the creator of the article, as the article is now nearly nine years old. I will, however, post my inquiry there and hope for a response. Thank you for directing me to the correct location.
|
|
|
|
|
I have a service that runs as Local System. I need the service to be aware when the machine goes to sleep and when it reawakens.
Since the service has no UI, hence no windows, is there some other facility in the framework for receiving the necessary broadcast messages?
SOLUTION:
The ServiceBase class has an protected OnPowerEvent() function that I can override.
Thanks for reading my post.
The difficult we do right away...
...the impossible takes slightly longer.
modified 31-Jan-12 18:02pm.
|
|
|
|
|
Richard Andrew x64 wrote: no UI, hence no windows
Hmm. I've been using a hidden window in such occasions, not sure it would work for a service though.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
Thanks Luc, you have given me the answer even though you didn't state it directly. 
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
Did I? Sorry for that.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
And then there is the SystemEvents[^] class, it doesn't need a window!
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
Case Scenario:
Executable:
namespace ChipTools
{
public class SplashScreen
{
public int ReadChip()
{
byte[] data = new byte[32];
Assembly a = Assembly.LoadFrom("Reader.dll");
Type mm = a.GetType("Reader");
object o = Activator.CreateInstance(mm);
object[] par = new object[] { data };
int status = (int)mm.InvokeMember("ReadChip", BindingFlags.Default | BindingFlags.InvokeMethod, null, o, par));
}
}
}
Reader.dll (.NET dll)
namespace ChipReader
{
public class Reader
{
public int ReadChip(byte[] data)
{
int status = 0;
<reads chip>
return status;
}
}
}
Question is... Did I do this right? If not, should I load the appropriate namespace in order for this late binding to work? Pls give example so I can understand. Thanks!
|
|
|
|
|
I just figured it out... it should read for the executable... The correction is at "Type mm" line in the code below.
namespace ChipTools
{
public class SplashScreen
{
public int ReadChip()
{
byte[] data = new byte[32];
Assembly a = Assembly.LoadFrom("Reader.dll");
Type mm = a.GetType("ChipReader.Reader");
object o = Activator.CreateInstance(mm);
object[] par = new object[] { data };
int status = (int)mm.InvokeMember("ReadChip", BindingFlags.Default | BindingFlags.InvokeMethod, null, o, par));
}
}
}
|
|
|
|
|
Blubbo wrote: Did I do this right?
Does it work? If not, why don't you show the outcome, the exceptions, etc., rather than turn it into a quiz?
BTW: you need less reflection code, and you'll get better performance, when your late bound object implements an early bound interface.
Luc Pattyn [My Articles] Nil Volentibus Arduum
Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.
|
|
|
|
|
Luc Pattyn wrote: early bound interface
A little misleading. Although interfaces allow compilers to do compile-time contract checks, the actual method to be called is determined only at run-time.
|
|
|
|
|
The method is determined by the Framework at the time that you perform the cast to the known interface (typically immediately after calling Activator.CreateInstance). At the point you actually call the method, it's dispatched in the normal way for interface methods.
I second the recommendation to the OP to do it this way, if possible.
|
|
|
|
|
|
You don't need to load any namespace unless you want to load all types within a namespace.
E.g.
var q = from t in a.GetTypes()
where t.IsClass && t.Namespace == "ChipReader"
select t;
Reflection is a performance-intensive way to achieve late binding.
Unless you are studying reflection, you might also want to consider Interfaces as another means to achieve late binding.
As another example. an object is late bound when it is assigned to a variable declared to be of type Object.
|
|
|
|
|
I agree with Luc, you are better off doing the following:
class Executable {
public int ReadChip()
{
byte[] data = new byte[32];
Assembly a = Assembly.LoadFrom("Reader.dll");
Type mm = a.GetType("ChipReader.Reader");
IReader reader = (IReader)Activator.CreateInstance(mm);
int status = reader.ReadChip(data);
}
}
Somewhere both the reader DLL and the main app can reference*:
interface IReader {
int ReadChip(byte[] data);
}
DLL:
namespace ChipReader {
class Reader : IReader {
public int ReadChip(byte[] data){
}
}
}
This allows you to call methods on the reader directly without messing about with reflection code, apart from looking up and instantiating the original type. You can then pass the IReader instance around in the executable like any normal object.
*: If the reader DLLs are specific to this application, the interface can just be in the application, and the reader assembly would need to be compiled with a reference to the executable. This is typically a good approach for plugins, where they are plugging in to a known application. Otherwise, it needs to be in a separate interface assembly which both the app and the reader DLL should reference.
|
|
|
|
|
Ok I'll give this a shot. It does looks more simplier than what I do.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
