Tuesday, March 27, 2012
getting a constraint value from query in a trigger?
UPDATE table SET status = 1 WHERE id = 1000
if i have a trigger on that table, is there anyway i can get the id ?
i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?
thankshmm, looks like i can't even get the id if i try to update since it's an identity column|||How about:SELECT id FROM inserted-PatP|||inserted AND deleted are full blown copies of the table that's affected for the rows that are be modified or added.
You [Id] should be there
What are you trying to do?|||oops, actually i just realized SELECT id from inserted works fine even though id isn't updated
thanks
Friday, March 23, 2012
GetDate() in Constraint using user's system time, Stored Proc using Server's.
All,
I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().
I performed the following test:
1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.
2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.
*note: These servers are on separate domains and therefore could have different system times.
This causes my select stored procedure to NOT return the values I just entered.
Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?
If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||Todd:
Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.
Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.
sql