locked
trigger for a table contains all the record RRS feed

  • Question

  • 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.
    • Moved by Iric Wen Friday, September 21, 2012 1:42 AM (From:.NET Framework inside SQL Server)
    Thursday, September 20, 2012 12:32 PM

Answers

  • Hi Jason,

    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



    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jason Partner Sunday, September 23, 2012 5:04 AM
    Friday, September 21, 2012 1:42 AM

All replies

  • I’d suggest writing this trigger in T-SQL code rather than SQLCLR code.
     
    There is a separate forum for T-SQL code here: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. If you’d like, I can move this forum thread to that forum.
     
    Cheers, Bob
    Thursday, September 20, 2012 8:09 PM
  • Hi Jason,

    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



    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Jason Partner Sunday, September 23, 2012 5:04 AM
    Friday, September 21, 2012 1:42 AM