473,320 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

trigger to parse a string from insert statement

I have two tables:

tb_news--
-story_id
-productlist

tb_lookup--
-story_id
-product

when an insert command is run on tb_news, productlist field is
populated with a value such as 'abc, def, de'

when this happens, i need tb_lookup to be populated with seperate
records for each product in productlist and the story_id from tb_news.

Example:
INSERT INTO tb_news (story_id, product_list)
VALUES (12345, 'abc, def, de')

Results:
tb_news--
12345, 'abc, def, de'

tb_lookup--
12345, 'abc'
12345, 'def'
12345, 'de'
Ideally, I would like this to use recursion and give me tha ability to
change the delimiter at any time (might not always be a comma). some
products may have a period in them. number of products is unknown and
might be 0 (field may be empty or NULL).

Mar 22 '06 #1
2 1747
Hi
I'd prefer doing such things on the client side
create table w
(
id int,
t varchar(50)
)

insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @id int)
returns varchar(100)
as
begin
declare @w varchar(100)
set @w=''
select @w=@w+t+',' from w where id=@id
return @w
end

select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd

drop function dbo.fn_my
<us******@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...
I have two tables:

tb_news--
-story_id
-productlist

tb_lookup--
-story_id
-product

when an insert command is run on tb_news, productlist field is
populated with a value such as 'abc, def, de'

when this happens, i need tb_lookup to be populated with seperate
records for each product in productlist and the story_id from tb_news.

Example:
INSERT INTO tb_news (story_id, product_list)
VALUES (12345, 'abc, def, de')

Results:
tb_news--
12345, 'abc, def, de'

tb_lookup--
12345, 'abc'
12345, 'def'
12345, 'de'
Ideally, I would like this to use recursion and give me tha ability to
change the delimiter at any time (might not always be a comma). some
products may have a period in them. number of products is unknown and
might be 0 (field may be empty or NULL).

Mar 22 '06 #2
See if this helps:
http://groups.google.com/group/micro...91147f53af5fd9

--
Anith
Mar 22 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
33
by: coosa | last post by:
I have a table: ---------------------------------------------------- CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL,...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as...
2
by: Karl O. Pinc | last post by:
I'm sure I saw something like this on the postgresql web site but the the search function is down in the documentation area. I'm unable to pass a function arguments in a CREATE TRIGGER...
2
by: mob1012 via DBMonster.com | last post by:
Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is...
5
by: wpellett | last post by:
I can not get the SQL compiler to rewrite my SQL UPDATE statement to include columns being SET in a Stored Procedure being called from a BEFORE UPDATE trigger. Example: create table...
1
by: filip1150 | last post by:
I'm trying to find if there is any performance diference between explicitly using a sequence in the insert statement to generate values for a column and doing this in an insert trigger. I...
6
by: Oliver | last post by:
I'm fairly new to DB2. I have been assigned to build a delete trigger that finds the data type of each of the table's fields so that the trigger can then build a string consisting of OLD values...
10
by: JohnO | last post by:
Hi All, This question is related to iSeries V5R4 and db2. I want to implement an AFTER DELETE trigger to save the deleted rows to an archive table, I initially defined it as a FOR EACH...
7
by: anu b | last post by:
Hi I need to use Clr trigger for insert command My code is as below I am using SQL server 2005 and VS 2008.... but after running this code i didnt get the result as i expexted it shows the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.