none
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

    AFTER INSERT

    AS

    BEGIN

    SET NOCOUNT ON;

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

    END

    Wednesday, September 15, 2010 8:49 AM

Answers

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 | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, September 15, 2010 9:10 AM
    Moderator
  • I am explaining you whole scenario.I have one master table in which any time new records may comes.so 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

    for AFTER INSERT

    BEGIN

    INSERT INTO AnotherDB..MasTable(name, value)

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

     

    Check more details in http://msdn.microsoft.com/en-us/library/aa258254%28SQL.80%29.aspx


    Ramesh S
    www.sequalserver.com
    • 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;
    go

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 15, 2010 10:26 AM
    Moderator
  • 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)

    Go


    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 | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, September 15, 2010 11:47 AM
    Moderator