CODEWITHSUNDEEP
sqlsql-serverdatabasestored-procedurestriggers
Abdul Hamid
Abdul Hamid

Reputation: 53

How to fire trigger after every insert in sql server table automatically?

I am new to SQL Server triggers. I am facing a problem recently that I have two tables named tbl_Item and tbl_changePrice. I want to update tbl_Item when tbl_changeprice is inserted new row. With this new row name same date data will be updated in tbl_item table.

Here is my trigger what is tried by me for updating the table:

Alter TRIGGER trgAfterInsert ON [dbo].[tbl_changePrice] 
After insert
AS
    declare @itemname int;
    declare @saleprice decimal(18,2);
    declare @buyprice decimal(18,2);

    select @itemname=i.name from inserted i;    
    select @saleprice=i.saleprice from inserted i;  
    select @buyprice=i.pprice from inserted i;  

    update tbl_Item set sellingPrice= @saleprice, buyingPrice= @buyprice where name= @itemname

    PRINT 'AFTER INSERT trigger fired.'
GO

Upvotes: 1

Views: 4410

Answers (1)

marc_s
marc_s

Reputation: 754518

To handle multiple rows being inserted at once - you need to rewrite your trigger to be able to deal with multiple rows in Inserted - something like this:

ALTER TRIGGER trgAfterInsert 
ON [dbo].[tbl_changePrice] 
AFTER INSERT
AS
    UPDATE dbo.tbl_Item 
    SET sellingPrice = i.saleprice, buyingPrice = i.pprice 
    FROM Inserted i
    WHERE tbl_Item.name = i.name

    PRINT 'AFTER INSERT trigger fired.'
GO

Upvotes: 5

Related Questions