CODEWITHSUNDEEP
sqlsql-server
Abhishek Singh
Abhishek Singh

Reputation: 111

Create a trigger to insert records from a table to another one. Get inserted values in a trigger

I have two tables tbl_PurchaseDetails and tbl_ItemDetails. I need to insert some rows into tbl_ItemDetails from tbl_PurchaseDetails, right after it is inserted in tbl_PurchaseDetails. I know the problem but i am unable to solve it. Please help.

I have written the following code for the trigger:

CREATE TRIGGER trigger_UpdateItemDetails ON tbl_PurchaseDetails
FOR INSERT AS
DECLARE @PurchaseID VARCHAR(20)
DECLARE @Quantity INT
DECLARE @WarehouseID VARCHAR(20)

SELECT @PurchaseID=(PurchaseID) FROM INSERTED
SELECT @Quantity=(ItemQuantity) FROM INSERTED
SELECT @WarehouseID=(WarehouseID) FROM INSERTED

INSERT INTO 
tbl_ItemDetails
(PurchaseID,Quantity,WarehouseID)
VALUES
(
@PurchaseID,@Quantity,@WarehouseID
)

And now when I insert into tbl_PurchaseDetails the rows are added to tbl_PurchaseDetails but not to tbl_ItemDetails. It throws the following error:

Msg 515, Level 16, State 2, Procedure trigger_UpdateItemDetails, Line 11
Cannot insert the value NULL into column 'PurchaseID', table 'dbStockHandling.dbo.tbl_ItemDetails'; column does not allow nulls. INSERT fails.

My question is how to get the inserted values from tbl_PurchaseDetails so that the trigger can insert them into tbl_ItemDetails?

Upvotes: 4

Views: 53851

Answers (1)

TechDo
TechDo

Reputation: 18629

Please try:

CREATE TRIGGER trigger_UpdateItemDetails ON tbl_PurchaseDetails
FOR INSERT AS
BEGIN

    INSERT INTO 
    tbl_ItemDetails
    (
        PurchaseID,
        Quantity,
        WarehouseID
    )
    SELECT 
        PurchaseID, 
        ItemQuantity, 
        WarehouseID
    FROM 
        INSERTED
END

and make sure that you are inserting a NOT NULL value to column PurchaseID of table tbl_PurchaseDetails.

Upvotes: 6

Related Questions