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

you can use a cursor as follows:

create trigger trg_insertstuff
on [O_SALESMAN]
after insert
as

  DECLARE @SLSMAN_CD NVARCHAR(20)
    DECLARE @SLSMAN_NAME NVARCHAR(20)


 declare db_cursor CURSOR FOR  
SELECT SLSMAN_CD, SLSMAN_NAME
from inserted

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @SLSMAN_CD , @SLSMAN_NAME 

WHILE @@FETCH_STATUS = 0  
BEGIN  

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


FETCH NEXT FROM db_cursor INTO  @SLSMAN_CD , @SLSMAN_NAME 
end

CLOSE db_cursor  
DEALLOCATE db_cursor

Answer 2

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