Solved

microsoft, sql server, databases, case in trigger

Posted on 2008-06-11
3
184 Views
Last Modified: 2008-06-11
folks

i have a trigger that pics up the value of the id field in one table and then has to create records into another table based on this,
note the value of the id field in table a determines different inserts into table b i.e.
if the value starts with w-1 then it needs to insert 2 records into table b

i.e  
insert into b (id,number,type)(select id,@number ,'hours' from inserted)
insert into b (id,number,type)(select id,@number,'meter' from inserted)

if the value starts with b-1 then it needs to insert 2 records into table b

insert into b (id,number,type)(select id,@number ,'moves' from inserted)
insert into b (id,number,type)(select id,@number,'miles' from inserted)

else if the id doesnt start with these values then it isnt required to perform any inserts into table b

note the @number is based on the following:

DECLARE @number int
SELECT @number =MAX(number)+1 FROM table b

how do i build a trigger to achieve this?
0
Comment
Question by:rutgermons
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
jcoehoorn earned 500 total points
ID: 21759927
select id,@number ,CASE WHEN id LIKE 'w-1%' THEN 'hours' ELSE WHEN id LIKE 'b-1%' THEN 'moves' END from inserted
0
 

Author Comment

by:rutgermons
ID: 21759967
jcoe

thats wrong ,i need multiple inserts ie if my id like w-1 then 2 inserts need to be made

insert into b (id,number,type)(select id,@number ,'hours' from inserted)
insert into b (id,number,type)(select id,@number,'meter' from inserted)

if the value starts with b-1 then it needs to insert 2 records into table b

insert into b (id,number,type)(select id,@number ,'moves' from inserted)
insert into b (id,number,type)(select id,@number,'miles' from inserted)

all help will do
0
 
LVL 18

Expert Comment

by:jcoehoorn
ID: 21760191
That was just an example.  I expected you would use it as a template to build another set for next query.  Of course, it does assume that you've presented the entire problem, or it not at least that there are the same number of inserts for each id case.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question