trigger to insert row in another table when updated in one table

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)

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

 

Answer 6

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

Answer 7

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

Answer 8

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.......

Answer 9

 

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