how can insert only new record into another table through trigger RRS feed

  • Question

  • I have on master table in sql in which every time new data is inserted.i just want to copy the new inserted record into another table.How can i do this. below is my trigger but how can it identify only new records should be inserted into second table.please help me and modify it.

    CREATE TRIGGER CopyRecordToPortInTable ON SmsTable





    insert into Portintable(smsno,smstext,flag) select (sms,smstext,0) where condition


    Wednesday, September 15, 2010 8:49 AM


All replies

  • How do you define "new record"? If you insert a row into the SmsTable table, then by definition that row didn't exist before. This is why I ask what criteria you use to determine whether a record (row) is "new" or not.
    Tibor Karaszi, SQL Server MVP | |
    Wednesday, September 15, 2010 9:10 AM
  • I am explaining you whole scenario.I have one master table in which any time new records may I need a trigger which will copy those records into another table which have same structure as master table.
    Wednesday, September 15, 2010 9:44 AM
  • Hi,

    The below script will help you

    create trigger testinsert



    INSERT INTO AnotherDB..MasTable(name, value)

    SELECT name, value FROM inserted  --- inserted is holding newly inserted values


    Check more details in

    Ramesh S
    • Proposed as answer by RafMSDN Wednesday, September 15, 2010 12:28 PM
    • Marked as answer by Manoj Ruwali Monday, September 20, 2010 4:54 AM
    Wednesday, September 15, 2010 9:59 AM
  • This code can be re-written in SQL Server 2005 using the OUTPUT clause like below:

    create table itest ( i int identity not null primary key, j int not null unique )
    create table #new ( i int not null, j int not null)

    insert into itest (j)
    output inserted.i, inserted.j into #new
    select o.object_id from sys.objects as o

    select * from #new
    drop table #new, itest;


    Best Regards, Uri Dimant SQL Server MVP
    Wednesday, September 15, 2010 10:26 AM
  • Hi,

    Suppose you are inserting a data in a table called 'Table1 ' having two columns as 'rollno ' and 'marks1 '.the inserted data in a Table1 are storing in a table called 'Table2 ' having two columns as 'rollno ' and 'marks1 '

    Create trigger triggerName on Table1 for insert As

    --variables are declaring
    Declare @rollno int 
    Declare @marks1 int

    --storing the inserted record value in a varaible set @rollno=(select rollno from inserted
    set @marks1=(select marks1 from  inserted)

    insert into Table12(rollno,marks1) values(@rollno,@marks1)


    PS.Shakeer Hussain Hyderabad
    Wednesday, September 15, 2010 10:59 AM
  • Watch out! This doesn't handle multi-row inserts. Only one of the rows will be reflected in the log table.
    Tibor Karaszi, SQL Server MVP | |
    Wednesday, September 15, 2010 11:47 AM