December 5, 2011 at 6:02 am
Soz, Will do!
December 5, 2011 at 9:56 am
Dev (12/5/2011)
But a much better approach would be using the COLUMNS_UPDATED function.
http://msdn.microsoft.com/en-us/library/ms186329.aspx%5B/quote%5D
It requires bit comparison. If OP is comfortable with it, GO Ahead. I usually avoid it (my discomfort).
Side note on this. If the column is listed in the UPDATE clause of the calling statement, this bit is turned on. It does NOT check for data-changes, it merely checks if the column was used in the UPDATE.
IE: UPDATE tbl SET cola = 1, colb=1, colc=1 WHERE cola=1
This will activate all three, cola, colb, and colc, bits, even though it's (nearly) impossible that colA had changed.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 6, 2011 at 12:49 pm
Kyle,
The trigger as it stands won't do anything for an INSERT since the inserted table won't join to the deleted table. The IF UPDATE(STK_COSTPRICE) will always test true for an insert. If you actually want the update to occur for an INSERT then you could use EXCEPT between the inserted and deleted tables:
ALTER TRIGGER UDEF_COSTPRICE_UPDATE_DATE_KWD
ON stk_STOCK AFTER INSERT, UPDATE
AS
IF UPDATE(STK_COSTPRICE)
BEGIN
SET NOCOUNT ON ;
UPDATES
SET S.STK_P_WGHT_NAME = GETDATE()
FROM STK_STOCK S
INNER JOIN
(SELECT i.STK_PRIMARY, i.STK_COSTPRICE
FROM inserted i
EXCEPT
SELECT d.STK_PRIMARY, d.STK_COSTPRICE
FROM deleted d
) AS X ON
S.STK_PRIMARY = X.STK_PRIMARY
END
Todd Fifield
December 7, 2011 at 1:22 am
Thanks for that Todd. Will make the adjustment
December 7, 2011 at 10:10 am
Glad to help.
Todd Fifield
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply