December 5, 2011 at 1:36 am
I have written an update trigger for when the costprice is inserted or updated.
The issue I am having is that it updates if I change anything on that stock record not just the costprice.
What have I missed???
alter TRIGGER [dbo].[UDEF_COSTPRICE_UPDATE_DATE_KWD]
ON stk_STOCK
for INSERT,UPDATE
AS
IF UPDATE (STK_COSTPRICE)
BEGIN
SET NOCOUNT ON;
update stk_stock
set STK_P_WGHT_NAME= GETDATE()
FROM STK_STOCK INNER JOIN
inserted ON STK_STOCK.STK_PRIMARY = inserted.STK_PRIMARY
END
December 5, 2011 at 1:45 am
Please add following...
AND INSERTED.costprice != DELETED.costprice
December 5, 2011 at 1:47 am
Is this near the IF Update or the From inserted etc.?
December 5, 2011 at 1:48 am
kyle.doouss (12/5/2011)
Is this near the IF Update or the From inserted etc.?
in your update query...
December 5, 2011 at 1:57 am
Dev (12/5/2011)
Please add following...
AND INSERTED.costprice != UPDATED. costprice
Since there is no table "updated" I assume you meant to use the following:
AND INSERTED.costprice != deleted.costprice
But a much better approach would be using the COLUMNS_UPDATED function.
http://msdn.microsoft.com/en-us/library/ms186329.aspx
[font="Verdana"]Markus Bohse[/font]
December 5, 2011 at 2:00 am
MarkusB (12/5/2011)
Dev (12/5/2011)
Please add following...
AND INSERTED.costprice != UPDATED. costpriceSince there is no table "updated" I assume you meant to use the following:
AND INSERTED.costprice != deleted.costpriceBut a much better approach would be using the COLUMNS_UPDATED function.
Yup... I corrected it immediately as soon I realized it. You have old copy of my post 😛
December 5, 2011 at 2:04 am
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).
December 5, 2011 at 2:11 am
Thanks , but I can't seem to get it in the right place. Comments Please!
alter TRIGGER [dbo].[UDEF_COSTPRICE_UPDATE_DATE_KWD]
ON stk_STOCK
for INSERT,UPDATE
AS
IF UPDATE (STK_COSTPRICE)AND INSERTED.costprice != deleted.costprice
BEGIN
SET NOCOUNT ON;
update stk_stock
set STK_P_WGHT_NAME= GETDATE()
FROM STK_STOCK INNER JOIN
inserted ON STK_STOCK.STK_PRIMARY = inserted.STK_PRIMARY
END
December 5, 2011 at 2:52 am
A bit of a neater way.
ALTER TRIGGER [dbo].[UDEF_COSTPRICE_UPDATE_DATE_KWD] ON stk_STOCK
FOR INSERT, UPDATE
AS
IF UPDATE(STK_COSTPRICE)
BEGIN
SET NOCOUNT ON ;
UPDATE stk_stock
SET STK_P_WGHT_NAME = GETDATE()
FROM STK_STOCK
WHERE STK_STOCK.STK_PRIMARY IN (
SELECT i.STK_PRIMARY
FROM inserted i
INNER JOIN deleted d ON i.STK_PRIMARY = d.STK_PRIMARY
WHERE i.costprice != d.costprice ) ;
END
Edit: corrected aliases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 5, 2011 at 5:36 am
I am getting the following errors??
Msg 1011, Level 16, State 1, Procedure UDEF_COSTPRICE_UPDATE_DATE_KWD, Line 20
The correlation name 'i' is specified multiple times in a FROM clause.
ALTER TRIGGER [dbo].[UDEF_COSTPRICE_UPDATE_DATE_KWD] ON stk_STOCK
FOR INSERT, UPDATE
AS
IF UPDATE(STK_COSTPRICE)
BEGIN
SET NOCOUNT ON ;
UPDATE stk_stock
SET STK_P_WGHT_NAME = GETDATE()
FROM STK_STOCK
WHERE STK_STOCK.STK_PRIMARY IN (
SELECT STK_PRIMARY
FROM inserted i
INNER JOIN deleted i ON i.STK_PRIMARY = d.STK_PRIMARY
WHERE inserted.costprice != deleted.costprice ) ;
END
December 5, 2011 at 5:44 am
Its just a small change with the alias name. The alias name assigned to the tables, inserted and deleted were the same and they were in s singe derived table.
UPDATE stk_stock
SET STK_P_WGHT_NAME = GETDATE()
FROM STK_STOCK
WHERE STK_STOCK.STK_PRIMARY IN (
SELECT STK_PRIMARY
FROM inserted i
INNER JOIN deleted d ON i.STK_PRIMARY = d.STK_PRIMARY
WHERE i.costprice != d.costprice ) ;
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 5, 2011 at 5:48 am
I am now getting the following message. There is a column name STK_PRIMARY
this is in the STK_STOCK table???
Msg 209, Level 16, State 1, Procedure UDEF_COSTPRICE_UPDATE_DATE_KWD, Line 24
Ambiguous column name 'STK_PRIMARY'.
December 5, 2011 at 5:50 am
UPDATE stk_stock
SET STK_P_WGHT_NAME = GETDATE()
FROM STK_STOCK
WHERE STK_STOCK.STK_PRIMARY IN (
SELECT i.STK_PRIMARY
FROM inserted i
INNER JOIN deleted d ON i.STK_PRIMARY = d.STK_PRIMARY
WHERE i.costprice != d.costprice ) ;
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 5, 2011 at 6:00 am
Thanks for your lighting response
All working now.
Cheers
December 5, 2011 at 6:01 am
Btw, if you want tested and working code in the future, post the table definitions so that we can test the code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply