none
trigger to insert row in another table when updated in one table RRS feed

  • Question

  • hi all

    I have a table in sql in which every time new data is inserted some time i update this table.i just want to copy the new inserted and updated record into another table.How can i do this. .please help me. this trigger is working fine when inserting data into one table ond copy into another table but when i update first table and insert row in another table with only modified column into first table 

    Alter TRIGGER [dbo].[UpdateBalance]
       ON  [dbo].[Items]
       AFTER INSERT
    AS
    BEGIN
     
    SET NOCOUNT ON;

     DECLARE @RecordID BIGINT

     DECLARE @Debit VARCHAR(50)
     DECLARE @Credit VARCHAR(50)
     DECLARE @Status VARCHAR(50)

     SET @RecordID=(SELECT Id FROM INSERTED)
     
     SET @Debit=convert(varchar,(SELECT Debit FROM INSERTED))
     SET @Credit=(SELECT Credit FROM INSERTED)
        SET @Status=(SELECT [Status] FROM INSERTED)

            insert into Inhand(Onhand) values(@Credit)

    Monday, June 13, 2011 6:06 AM

All replies

  • hi all

    I have a table in sql in which every time new data is inserted some time i update this table.i just want to copy the new inserted and updated record into another table.How can i do this. .please help me. this trigger is working fine when inserting data into one table ond copy into another table but when i update first table and insert row in another table with only modified column into first table 

    AltAlter TRIGGER [dbo].[UpdateBalance]
       ON  [dbo].[Items]
       AFTER INSERT
    AS
    BEGIN
     
    SET NOCOUNT ON;

     DECLARE @RecordID BIGINT

     DECLARE @Debit VARCHAR(50)
     DECLARE @Credit VARCHAR(50)
     DECLARE @Status VARCHAR(50)

     SET @RecordID=(SELECT Id FROM INSERTED)
     
     SET @Debit=convert(varchar,(SELECT Debit FROM INSERTED))
     SET @Credit=(SELECT Credit FROM INSERTED)
        SET @Status=(SELECT [Status] FROM INSERTED)

        case when  @Status='1' then
        insert into Inhand(Onhand) values(@Credit)

    Monday, June 13, 2011 5:56 AM
  • Can you please provide create table + sample insert for data?
    This will help us in getting to solution quickly.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, June 13, 2011 6:07 AM
    Moderator
  • Hi,

    You can use the output clause like this

    update table set col=value

    output inserted.* into table1

     

    Or

    you can create an Update trigger

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

    http://msdn.microsoft.com/en-us/library/ms187326.aspx


    Thanks and regards, Rishabh , Microsoft Community Contributor
    Monday, June 13, 2011 6:13 AM
  • Avoid posting to multiple forums. I am merging both threads in T-SQL Forum.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Monday, June 13, 2011 6:13 AM
    Moderator
  • hi

    thanks for reply me

    this is first table

     items

    (Debit bigint, Credit bigint, [Status] varchar(50))

    second table is (Inhand) column name (onhand)

    when we inserting data in items table with status 1

    then data should be copy in second table

    when we change the status of first table

    then data should be copy in Onhand table with credit column data in first table

     

    Monday, June 13, 2011 6:20 AM
  • hi thanks to reply me but i have to insert a copy of updated row (which is updated in first table) but before insertion in second table we have to chack a condition with status just like that if status=1 then copy of upated row should be inserted in secoond table other that should not be insert please help me reply me as soon as possible its urgent thanks in advance
    Monday, June 13, 2011 7:21 AM
  • Try this....

    Alter TRIGGER [dbo].[UpdateBalance]
       ON  [dbo].[Items]
    FOR INSERT , UPDATE

    IF (Select Status From INSERTED) = 1

    BEGIN

                ---  Insert your here like, Insert to other table or update.....

    END

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/edeced03-decd-44c3-8c74-2c02f801d3e7.htm

     


    Muhammad Azeem
    Monday, June 13, 2011 9:41 AM
  • I want to update the status column in onetable when record inserted to another table using triggers

     Table 1 : Employee

    Table 2 : Department.

    Bothe the table contain CompanyID column

    If I insert record in Deparment table , the inserted company ID have to check with the Employee Table Company ID if it is matched then

    Status Column in Employee table should Update to "Arrived". Using sql trigger

    can any body help me out from this.......

    Thursday, August 4, 2011 3:20 AM
  •  

    create trigger trDepartmentInsert ON Department FOR INSERT
    
    AS
    
       IF not exists (select 1 from Inserted) -- insert didn't insert anything
    
              return
    
       update Employee SET Status = 'Arrived'
    
       FROM Employee INNER JOIN Inserted I ON Employee.CompanyID = I.CompanyID
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, August 4, 2011 3:31 AM
    Moderator