Hi
I want to write Trigger tha do this:
I have 2 table
main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.
or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.
I want write a Trigger that make a 3nd table that contain all the record of that two table like this:
NAME CODE
Bank 001
visa card 0010001
master card 0010002
BMW 101
X5 1010001
X3 1010002
..........................................
Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.
1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.
2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.
Something like what you see below. You could also use a computed column as explained in the other post.
Thanks
set nocount on
go
use tempdb
go
create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go
insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go
create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go
create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go
create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go
insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go
select * from combined order by the_combined_code
go
drop table submain
go
drop table main
go
drop table combined
go
