none
Auto udating data in multiple table through trigger.

    Question

  • table a(

    sno IDENTITY int(1),

    name varchar(5),

    roll_no numeric(5)

    sum more columns

    )

    table b(

    sno IDENTITY int(1),

    name varchar(5),

    sum more columns

    )

    table c(

    sno IDENTITY int(1),

    roll_no numeric(5)

    )

     

    Could any one kindly tell me how can i do it.

    whenever data is entered in table a the values of name and roll_no to be automatically filled in table b and table c with use of trigger.

    Tuesday, October 19, 2010 12:22 PM

Answers

All replies

  • CREATE TRIGGER TTT
    ON a
    AFTER INSERT 
    AS 
    BEGIN
    insert b (name) select name from inserted
    insert c (roll_no) select roll_no from inserted
    END
    

     

     


    Best regards
    Tuesday, October 19, 2010 12:33 PM
  • The design is incorrect.

    You should NOT have identity columns in Table b and Table c. They should be referenced by Table a and should be int columns without identity.

    Another thing is that how will you handle updates? You have to write AFTER INSERT, UPDATE Trigger.

    In trigger you shoud check the existing Serial numbers (SNOs) and Update them and Insert the new ones. Simple will be delete the Old one and Insert new one.

    Like this;

     

    CREATE TRIGGER trg_a
    ON dbo.a
    AFTER INSERT, UPDATE
    AS
    BEGIN
    	Delete from b where sno in (Select sno from inserted)
    	Delete from c where sno in (Select sno from inserted)
    	
    	insert b (sno,name) select sno,name from inserted
    	insert c (sno,roll_no) select sno,roll_no from inserted
    END

    Try this on Development server.

     

     

     


    Please visit my Blog for some easy and often used t-sql scripts
    Tuesday, October 19, 2010 1:04 PM
  • Actually i am using these tables data in data grid .

    and for filling datagrid it needs a IDENTITY column so cant remove it.

    Tuesday, October 19, 2010 1:12 PM
  • can i also use a if clause in it like

    when inserting in table a

    if name="xyz" then

        insert b(name ) select sno,name from inserted.

    else if name="abc" then

           insert d(name ) select sno,name from inserted.

     

     

    Tuesday, October 19, 2010 1:28 PM
  • I think that you can use this strcture and you use the Trigger of Rami:

     

    table a(
    sno_I IDENTITY int(1),
    sno int,
    name varchar(5),
    roll_no numeric(5)
    sum more columns
    )
    table b(
    sno_I IDENTITY int(1),
    sno int,
    name varchar(5),
    sum more columns
    )
    table c(
    sno_I IDENTITY int(1),
    sno int,
    roll_no numeric(5)
    )
    

    Best regards
    Tuesday, October 19, 2010 1:37 PM
  • how can i use if conditions in a trigger?
    Tuesday, October 19, 2010 5:16 PM
  • If <condition>
    Begin
    <query>
    End
    

     You can see this link :

    http://msdn.microsoft.com/fr-fr/library/ms182717.aspx

     

    in trigger, example :

    CREATE TRIGGER TTT
    ON a
    AFTER INSERT 
    AS 
    BEGIN
    declare @name varchar(5)
    select @name=[name] from inserted
    if @name='xyz'
    begin
    insert b (name) select name from inserted
    end
    END

     


    Best regards

     

     

    • Marked as answer by ankitguptajpr Wednesday, October 20, 2010 4:21 PM
    Tuesday, October 19, 2010 5:35 PM