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)
UPDATEmt
SETmt.HEIGHT = i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
FROMSALES_ORDER_LINE AS mt
INNER JOINinserted 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)
UPDATEmt
SETmt.HEIGHT = i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
FROMSALES_ORDER_LINE AS mt
INNER JOINinserted 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)
UPDATEmt
SETmt.HEIGHT =
CASE
WHEN DRAWING_ID <> 'set' THEN i.DRAWING_REV_NO * (i.ORDER_QTY / i.BOX_QTY)
ELSE i.DRAWING_REV_NO
END
FROMSALES_ORDER_LINE AS mt
INNER JOINinserted 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)
UPDATEmt
SETmt.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
FROMSALES_ORDER_LINE AS mt
INNER JOINinserted 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy