February 3, 2012 at 1:17 am
Good day guys.
Can anyone please guide me. I'm working on a nested IF ELSE on a trigger (not sure how you call it). Anyway, I have 3 columns RECORD_ID, PACK_ID and QTY on MyTable
I need a trigger to fill the data on QTY based on PACK_ID
If my PACK_ID = 1/1 QTY must be 1
If my PACK_ID = 1/2 QTY must be 2
If my PACK_ID = 1/3 QTY must be 3
and so on...
I'm new to SQL and still figuring it out. Thanks
February 3, 2012 at 2:00 am
Please will you provide DDL for your table, along with sample data. Depending on your data types, you could use mathematical functions, or a CASE expression.
John
February 3, 2012 at 8:56 pm
Table Build:
CREATE TABLE PACKAGING
(RECORD_ID INT IDENTITY(1,1) PRIMARY KEY,
PACK_ID varchar(4),
BOX_QTY numeric(10,0)
Data:
use TEST01
insert into PACKAGING
values('1/1', NULL)
insert into PACKAGING
values('1/2', NULL)
insert into PACKAGING
values('1/3', NULL)
insert into PACKAGING
values('1/4', NULL)
insert into PACKAGING
values('1/5', NULL)
insert into PACKAGING
values('1/6', NULL)
insert into PACKAGING
values('1/7', NULL)
insert into PACKAGING
values('1/8', NULL)
insert into PACKAGING
values('1/9', NULL)
insert into PACKAGING
values('1/10', NULL)
I need to formulate a trigger that will populate the BOX_QTY column based on PACK_ID values.
Equivalent values:
1/1 = 1
1/2 = 2
1/3 = 3
1/4 = 4
1/5 = 5
1/6 = 6
1/7 = 7
1/8 = 8
1/9 = 9
1/10 = 10
February 6, 2012 at 1:39 am
Have you read about computed columns? I think this would be a better solution than a trigger.
John
February 6, 2012 at 8:04 pm
I've tried it but i'm having difficulties building a formula.
Can you please show me how?
thanks
February 6, 2012 at 9:42 pm
If you want to use the Computed Column approach(as per
John Mitchell said), you can try this.
--Note: if your table is still empty, do the following script
--drop the column BOX_QTY
alter table PACKAGING
drop column BOX_QTY
go
--add the BOX_QTY with computed values
alter table PACKAGING
add BOX_QTY as convert(int, substring(PACK_ID, charindex('/', PACK_ID) + 1, len(PACK_ID) - charindex('/', PACK_ID)))
"Often speak with code not with word,
A simple solution for a simple question"
February 7, 2012 at 6:40 pm
mhike2hale, the code is working fine.
But, what if the data inputted on PACK_TYPE has been changed? Like 2/1 4/1...
The computed column will always detect 1
How can i make it recognize both 1/2 and 2/1 format? Will the calculated column still work?
I'm currently building a trigger with nested IF statements. Any suggestions? Anyone?
February 7, 2012 at 6:55 pm
Can you give me a sample BOX_QTY value if let say i have PACK_ID 2/5 or 3/6 or 4/4?
And do you have PACK_TYPE field on this table?
"Often speak with code not with word,
A simple solution for a simple question"
February 7, 2012 at 7:44 pm
Possible Values:
1/1
1/2
1/3
1/4
1/5
1/6
1/8
2/1
3/1
4/1
5/1
6/1
7/1
8/1
I'm thinking about CASE statement for e lesser code. What do you think?
February 7, 2012 at 7:57 pm
I've tried this but cannot seem to make it work.
CREATE TRIGGER boxqty
ON PACKAGING
AFTER INSERT, UPDATE, DELETE
AS
WITH BX AS (PACKAGING
select PACK_ID from inserted
union
select PACK_ID from deleted
)
UPDATE PACKAGING
SET
BOX_QTY =
case
when t.PACK_ID = '1/1' then 1
when t.PACK_ID = '1/2' then 2
when t.PACK_ID = '1/3' then 3
when t.PACK_ID = '1/4' then 4
when t.PACK_ID = '2/1' then 2
when t.PACK_ID = '3/1' then 3
when t.PACK_ID = '4/1' then 4
when t.PACK_ID IS NULL then NULL
end
FROM PACKAGING c
INNER JOIN BX ON BX.PACK_ID = c.PACK_ID
February 7, 2012 at 8:11 pm
Is there a possibility to have a value of 2/3?
I just notice that most of your PACK_ID examples have always 1 on numerator or denominator.
"Often speak with code not with word,
A simple solution for a simple question"
February 7, 2012 at 8:12 pm
Guys, thanks for the help. I'ts working now.
I changed my code to this:
ALTER TRIGGER [dbo].[boxqty]
ON [dbo].[PACKAGING]
AFTER INSERT, UPDATE, DELETE
AS
WITH BX AS (
select PACK_ID from inserted
union
select PACK_ID from deleted
)
UPDATE PACKAGING
SET
BOX_QTY =
case
when c.PACK_ID = '1/1' then 1
when c.PACK_ID = '1/2' then 2
when c.PACK_ID = '1/3' then 3
when c.PACK_ID = '1/4' then 4
when c.PACK_ID = '2/1' then 2
when c.PACK_ID = '3/1' then 3
when c.PACK_ID = '4/1' then 4
when c.PACK_ID IS NULL then NULL
end
FROM PACKAGING c
INNER JOIN BX ON BX.PACK_ID = c.PACK_ID
February 7, 2012 at 8:19 pm
its always 1
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply