November 27, 2007 at 9:21 pm
I will try to provide as much information as I can (last time I looked like a total NUB) 🙂
DATABASE: dbo.P21Play
TABLE:
[JemWorldshipExport]
FIELDS:
pick_ticket_no
void char
tracking_no
freight_out varchar (1.00)
Billing_Option varchar (1 or 2)
Ok so I am updating my db table with all of these fields accept for Billing_Option. What I am looking to do is
create a case statement that will look at my billing_option field (which will be either a 1 or a 2) and Change
my freight_Out field as following:
*If Billing_Option is equal to 2 then make the output of freight_out equal to Null
*If Billing_Option is equal to 1 then pass whatever was in freight_Out (ignore)
I am not sure yet how to write a CASE statement inside a trigger but I know its possible?
Here is my Trigger as it stands:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [JemWorldshipTrigger]
on [dbo].[JemWorldshipExport]
for insert as
DECLARE @pick_ticket_no decimal (19,0)
DECLARE @void varchar (1)
DECLARE @tracking_no varchar (40)
DECLARE @freight_out decimal (19,4)
DECLARE @Billing_Option varchar (50)
select @pick_ticket_no=pick_ticket_no,
@void=void,
@tracking_no=tracking_no,
@freight_out=freight_out from inserted
update P21PLAY.dbo.oe_pick_ticket
set P21PLAY.dbo.oe_pick_ticket.tracking_no=@tracking_no,
P21PLAY.dbo.oe_pick_ticket.freight_out= isnull(P21PLAY.dbo.oe_pick_ticket.freight_out,0) + @freight_out,
P21PLAY.dbo.oe_pick_ticket.date_last_modified=current_timestamp,
P21PLAY.dbo.oe_pick_ticket.last_maintained_by='Administrator'
where (P21PLAY.dbo.oe_pick_ticket.pick_ticket_no=@pick_ticket_no and @void='N')
update P21PLAY.dbo.oe_pick_ticket
set P21PLAY.dbo.oe_pick_ticket.tracking_no='UPS VOID',
P21PLAY.dbo.oe_pick_ticket.freight_out='0.0000',
P21PLAY.dbo.oe_pick_ticket.date_last_modified=current_timestamp,
P21PLAY.dbo.oe_pick_ticket.last_maintained_by='Administrator'
where P21PLAY.dbo.oe_pick_ticket.pick_ticket_no=@pick_ticket_no and @void='Y'
November 27, 2007 at 10:15 pm
Couple things.
Case statement is fairly simple.
Constructed 2 ways
always starts with the case keyword and ends with the end keyword.
you can make the expression 2 ways.
when you are just comparing values in one field then you can construct like this.
as soon as the case finds a true condition, then it exits and stops evaluating the other conditions.
Case Myfield when 'Y' then something when 'N' then Somethingelse end
or the other method you can compare whatever you want,
Case WHEN Myfield = 'Y' then something when yourfield = 'Y' then Somethingelse end.
Second, you must always construct your triggers to handle multiple Inserts or updates.
Your trigger is constructed to only handle one row insert at at time, even if you insert multiple rows the trigger will only work on one record. usually the last one.
I have rewritten your trigger so you can insert multiplerows. Even if you can control the number of inserts it is always good to write your triggers to handle many rows.
Because at some point you, or someone else will want to do maintenence to this table and forget about the trigger, and the trigger will not do its job.
Try this trigger, I tried to include you example for the case statement.
Also, always good to add comments to your code so you can quickly see what you were trying to acomplish with the codeblock.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER trigger [JemWorldshipTrigger]
on [dbo].[JemWorldshipExport]
for insert as
-- Update Tracking_No to void where JemworldshipExport.Void = 'y'
update A
A.tracking_no='UPS VOID',
A.freight_out='0.0000',
A.date_last_modified=current_timestamp,
A.last_maintained_by='Administrator'
From P21PLAY.dbo.oe_pick_ticket A
join inserted I on I.pick_ticket_no = a.pick_ticket_no
where I.Void = 'Y'
-- Update Tracking_no, FreightOut where void = 'n'
update A
set A.tracking_no=I.tracking_no,
A.freight_out= case I.Billing_Option when 2 then NULL when 1 then I.freight_out end,
A.date_last_modified=current_timestamp,
A.last_maintained_by='Administrator'
From P21PLAY.dbo.oe_pick_ticket A
join inserted I on I.pick_ticket_no = a.pick_ticket_no
where I.void='N'
Go
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply