Firing trigger for bulk insert

Question

ALTER TRIGGER [dbo].[TR_O_SALESMAN_INS] ON [dbo].[O_SALESMAN] AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

-- Insert statements for trigger here
DECLARE @SLSMAN_CD NVARCHAR(20)
DECLARE @SLSMAN_NAME NVARCHAR(20)
SELECT @SLSMAN_CD = SLSMAN_CD,@SLSMAN_NAME=SLSMAN_NAME
FROM INSERTED

IF NOT EXISTS(SELECT * FROM O_SALESMAN_USER WHERE SLSMAN_CD = @SLSMAN_CD)
BEGIN
    INSERT INTO O_SALESMAN_USER(SLSMAN_CD, PASSWORD, USER_CD)
    VALUES(@SLSMAN_CD, @SLSMAN_CD,@SLSMAN_NAME )
END

END

This is the trigger written for a table(O_SALESMAN) to fetch few columns from it and insert it into one another table(O_SALESMAN_USER). Presently bulk data is getting inserted into O_SALESMAN table through a stored procedure, where as the trigger is getting fired only once and O_SALESMAN_USER is having only one record inserted each time whenever the stored procedure is being executed,i want trigger to run after each and every record that gets inserted into O_SALESMAN such that both tables should have same count which is not happening..so please let me know what can be modified in this Trigger to achieve the same....

Answer 1

Yes, triggers fire once per statement, not once per row.

So you need something like:

SET NOCOUNT ON

INSERT INTO O_SALESMAN_USER(SLSMAN_CD, PASSWORD, USER_CD)
SELECT
    i.SLSMAN_CD,i.SLSMAN_CD,i.SLSMAN_NAME
FROM
    inserted i
        left join
    O_SALESMAN_USER u
        on
            i.SLSMAN_CD = u.SLSMAN_CD
where
    u.SLSMAN_CD is NULL

Answer 2

My scenerio is, (Table Names: Stock, StockLog)

I am inserting bulk rows inside Stock table through a Stored Procedure and want to also have all these rows inside StockLog table

first i was doing same as you (by variables) inside my insert trigger for Stock table but getting error because by using

DECLARE @StocklId bigint

SET @StocklId = (SELECT StocklId FROM inserted)

i was having multiple values by (SELECT StocklId FROM inserted) as i was inserting multiple rows, then i remove all variables, and now i am doing this

INSERT INTO StockLog(StocklId,PharmacyId,TransactionDetailId,ProductId,TotalQty,ReservedQty,AvailableQty,strUserName,strTerminalName,strVer)

SELECT StocklId, PharmacyId, TransactionDetailId, ProductId, TotalQty, 0, AvailableQty,strUserName, strTerminalName, strVer FROM inserted

and now everything is fine