February 9, 2012 at 11:18 pm
Hi guys. I have been experimenting (trying to learn) on trigger and made this code.
CREATETRIGGER [dbo].[linecbm] ON [dbo].[SALES_ORDER_LINE]
AFTER UPDATE
AS
IF UPDATE (DRAWING_REV_NO)
UPDATE mt
SET mt.HEIGHT = i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
FROM SALES_ORDER_LINE AS mt
INNER JOIN inserted AS i ON i.RECORD_IDENTITY = mt.RECORD_IDENTITY
Now I am trying modify this code by adding IF ELSE statement.
Something like this:
ALTER TRIGGER [dbo].[linecbm] ON [dbo].[SALES_ORDER_LINE]
AFTER UPDATE
AS
IF DRAWING_ID <> 'set' THEN
UPDATE (DRAWING_REV_NO)
UPDATE mt
SET mt.HEIGHT = i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
FROM SALES_ORDER_LINE AS mt
INNER JOIN inserted AS i ON i.RECORD_IDENTITY = mt.RECORD_IDENTITY
ELSE
UPDATE mt
SET mt.HEIGHT = i.DRAWING_REV_NO
Still figuring it out :crying:
any modification suggestions? Thanks
February 10, 2012 at 3:43 am
ALTER TRIGGER [dbo].[linecbm] ON [dbo].[SALES_ORDER_LINE]
AFTER UPDATE
AS
BEGIN
IF UPDATE (DRAWING_REV_NO)
UPDATE mt
SET mt.HEIGHT =
CASE
WHEN DRAWING_ID <> 'set' THEN i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
ELSE i.DRAWING_REV_NO
END
FROM SALES_ORDER_LINE AS mt
INNER JOIN inserted AS i ON i.RECORD_IDENTITY = mt.RECORD_IDENTITY
END
-- Gianluca Sartori
February 10, 2012 at 7:35 pm
I see, CASE is a better option.
getting an error: Ambiguous column name 'DRAWING_ID'
February 10, 2012 at 7:50 pm
Got it, forgot to put mt on the CASE statement.
Now its working fine and as expected. Thanks
ALTER TRIGGER [dbo].[linecbm] ON [dbo].[SALES_ORDER_LINE]
AFTER UPDATE
AS
IF UPDATE (DRAWING_REV_NO)
UPDATE mt
SET mt.HEIGHT =
CASE
WHEN mt.DRAWING_ID = 'set' THEN i.DRAWING_REV_NO
WHEN mt.DRAWING_ID <> 'set' THEN i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
END
FROM SALES_ORDER_LINE AS mt
INNER JOIN inserted AS i ON i.RECORD_IDENTITY = mt.RECORD_IDENTITY
February 11, 2012 at 2:52 pm
You're welcome. Glad I could help.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply