locked
Trigger to Insert and Update RRS feed

  • Question

  • Hi All, 
    I need to create a trigger,but bit confuse with the syntex .Working on [SQL 2008]
    I have two table "One" and "Two"
    Table

    Structure are

    Create table One (ID int, Name Nvarchar(50)) 

    Create Table Two (ID int ,Person Nvarchar(50)) 

    I need to create Trigger In a way that:

    When New Record is inserted into table One Then Same Should get Insert Table Two

    And

    when Data is Updated in Name column of "One" then same Update should happen in Table "Two" for Person Column

    i.e Both Insert and Update should happen on same time .

    Note:Here we can make a Join between Both the table based on ID column
     

    I tried so many things ,but some time I face problem with Update and some times with Insertion,Any help ??




    • Edited by MS308 Wednesday, November 30, 2011 7:29 AM
    Wednesday, November 30, 2011 7:15 AM

Answers

  • create table one (id int, name varchar(10))
    create table two (id int, person varchar(10))
    create trigger mytr on one for insert,update
    as
    if not exists (select * from deleted)
    begin 
      insert into two select * from inserted
    end
    else
     begin
       update two set person=i.name from inserted i 
                    join two  on two.id=i.id
     end
    --usage
    insert into one  select 100,'John'
    select * from two 
    update one  set name ='Alex' where id=100
    select * from two

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by MS308 Wednesday, November 30, 2011 7:48 AM
    Wednesday, November 30, 2011 7:42 AM
    Answerer

All replies

  • create table one (id int, name varchar(10))
    create table two (id int, person varchar(10))
    create trigger mytr on one for insert,update
    as
    if not exists (select * from deleted)
    begin 
      insert into two select * from inserted
    end
    else
     begin
       update two set person=i.name from inserted i 
                    join two  on two.id=i.id
     end
    --usage
    insert into one  select 100,'John'
    select * from two 
    update one  set name ='Alex' where id=100
    select * from two

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by MS308 Wednesday, November 30, 2011 7:48 AM
    Wednesday, November 30, 2011 7:42 AM
    Answerer
  • Thanks Uri Diamnt :)

     

    Wednesday, November 30, 2011 7:49 AM