insert trigger which put 10 rows in another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert trigger which put 10 rows in another table

Author

Topic

jylland
Starting Member

27 Posts

Posted - 2010-02-08 : 06:06:40

How can I do this, Every time I put a name to table A the trigger should insert 10 rows in table B, with at range (1-10), the table A's ID and 10 standard Names (See below)

I have tried this SQL but here I mised the standard names, here all name is null, but I want to put se 10 standard names (see below)

create trigger trg_insert
on tableA
for insert
as
insert into tableB ( id, range, name )
select i.id, h.r, null
from inserted i
cross join ( select 1 r union all select 2 union all select 3 union all select 4
union all select 5 union all select 7 union all select 8 union all select 9
union all select 6 union all select 10 ) h

Table A

ID Name
1 Ben
2 Hugo

Table B

ID Range Name
1 1 Jones
1 2 Peter
1 3 John
1 4 James
1 5 Lim
1 6 Johny
1 7 Lin
1 8 Jack
1 9 Nick
1 10 Poul
2 1 Jones
2 2 Peter
2 3 John
2 4 James
2 5 Lim
2 6 Johnny
2 7 Lin
2 8 Jack
2 9 Nick
2 10 Poul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-08 : 06:17:55

[code]
select i.id, h.r, h.name
from inserted i
cross join
(
select 1 r, 'Jones' as [name] union all
select 2, 'Peter' union all
select 3, 'John' union all
. . .
) h
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jylland
Starting Member

27 Posts

Posted - 2010-02-08 : 06:34:41

Thanks khtan
Great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources