INSERT Trigger for handling multi row insert

[RRinTetons]

I've got a trigger on a Table A to handle adding row to Table B based on rules about the newly inserted row in Table A. It works well when rows are entered one at a time via the user interface, but it fails when import many rows into a staging Table C and then try to insert then into A with

INSERT INTO TableA
      ....
      ....
      ....
SELECT * FROM TableC

The debugger show the first line of code in the trigger that attempt to extract a value fro INSERTED returning multiple rows.

So, in the body of an INSERTED trigger that's been hit with a multi row insert, how do I isolate each row for processing?

-
Richard Ray
Jackson Hole Mountain Resort

 

[bborissov]

All triggers must be programed to handle multiple rows. Triggers is fired after the whole job is done, not row by row.
So depending of your trigger logic you could use different way to handle this. If you post the current code for the trigger it will be easier for us to change it.


Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[RRinTetons]

Apologies for the length, I didn't want to leave out anything needed:

ALTER TRIGGER [dbo].[trgMaintainRoster_INSERT] 
   ON  [dbo].[Employee]   
   AFTER INSERT   
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @HireStatus INT
	SET @HireStatus = (	SELECT [e].[HireStatusID]
						FROM INSERTED AS [e]
						)					
	DECLARE @IsManager INT
	SET @IsManager = (	SELECT [e].[ManagerSupervisor]
						FROM INSERTED AS [e]
						)	
-------------------- Manager Training Insert ----------------------
IF @HireStatus = 1002 OR @HireStatus = 1006  --Employee is a New Hire or New Volunteer
	BEGIN
		IF @IsManager = 1 --If the employee is a Manager
			BEGIN
				INSERT INTO [dbo].[TrainingRoster]
							 (
							  [EmployeeID]
							 ,[DepartmentID]
							 ,[GroupID]
							 ,[ProgramID]
							 ,[TrainingStatusID]
							 ,[DateAdded]
							 ,[DateCompleted]
							 ,[TenantID]
							 )
				SELECT [e].[EmployeeID]
					,[e].[DepartmentID]
					,[g].[GroupID]
					,[p].[ProgramID]
					,1000 AS [TrainingStatusID]
					,GETDATE() AS [DateAdded]
					,NULL AS [DateCompleted]
					,[e].[TenantID]					
				FROM Inserted AS e
					JOIN [dbo].[Department] AS d
						ON [e].[DepartmentID] = [d].[DepartmentID]
					JOIN [dbo].[GroupDepartment] AS gd
						ON [d].[DepartmentID] = [gd].[DepartmentID]
					JOIN [dbo].[Group] AS g
						ON [gd].[GroupID] = [g].[GroupID]
							AND [g].[MangersSupervisors] = 1
					JOIN [dbo].[GroupProgram] AS gp
						ON [g].[GroupID] = [gp].[GroupID]
								AND [gp].[ProgramID] != 1122
					JOIN [dbo].[Program] AS p
						ON [gp].[ProgramID] = [p].[ProgramID]						
			END	--		IF @IsManager = 1																	
			-------------------- Non-manager Training Insert ----------------------
			IF @IsManager = 0 --If the newly hired employee or volunteer is NOT a manager		
				BEGIN
					INSERT INTO [dbo].[TrainingRoster]
								 (
								  [EmployeeID]
								 ,[DepartmentID]
								 ,[GroupID]
								 ,[ProgramID]
								 ,[TrainingStatusID]
								 ,[DateAdded]
								 ,[DateCompleted]
								 ,[TenantID]
								 )
					SELECT [e].[EmployeeID]
						,[e].[DepartmentID]
						,[g].[GroupID]
						,[p].[ProgramID]
						,1000 AS [TrainingStatusID]
						,GETDATE() AS [DateAdded]
						,NULL AS [DateCompleted]
						,[e].[TenantID]				
					FROM Inserted AS e
						JOIN [dbo].[Department] AS d
							ON [e].[DepartmentID] = [d].[DepartmentID]
						JOIN [dbo].[GroupDepartment] AS gd
							ON [d].[DepartmentID] = [gd].[DepartmentID]
						JOIN [dbo].[Group] AS g
							ON [gd].[GroupID] = [g].[GroupID]
								AND [g].[MangersSupervisors] = 0
						JOIN [dbo].[GroupProgram] AS gp
							ON [g].[GroupID] = [gp].[GroupID]
								AND [gp].[ProgramID] != 1122
						JOIN [dbo].[Program] AS p
							ON [gp].[ProgramID] = [p].[ProgramID]
				END --		IF @IsManager = 0	
	END --IF @HireStatus = 1002 OR @HireStatus = 1006
ELSE --Employee is a Returning Hire or Returning Volunteer
	BEGIN -- ELSE for IF @HireStatus = 1002 OR @HireStatus = 1006
		--------------------Returning Non-manager Training Insert ----------------------
	IF @IsManager = 0
	BEGIN
		INSERT INTO [dbo].[TrainingRoster]
						 (
						  [EmployeeID]
						 ,[DepartmentID]
						 ,[GroupID]
						 ,[ProgramID]
						 ,[TrainingStatusID]
						 ,[DateAdded]
						 ,[DateCompleted]
						 ,[TenantID]
						 )
			SELECT [e].[EmployeeID]
				,[e].[DepartmentID]
				,[g].[GroupID]
				,1122 AS [ProgramID]
				,1000 AS [TrainingStatusID]
				,GETDATE() AS [DateAdded]
				,NULL AS [DateCompleted]
				,[e].[TenantID]
					
			FROM Inserted AS e
				JOIN [dbo].[Department] AS d
					ON [e].[DepartmentID] = [d].[DepartmentID]
				JOIN [dbo].[GroupDepartment] AS gd
					ON [d].[DepartmentID] = [gd].[DepartmentID]
				JOIN [dbo].[Group] AS g
					ON [gd].[GroupID] = [g].[GroupID]
						AND [g].[MangersSupervisors] = 0
	END --IF @IsManager = 0	
-------------------- Returning Manager Training Insert ----------------------
	IF @IsManager = 1
	BEGIN
		INSERT INTO [dbo].[TrainingRoster]
						 (
						  [EmployeeID]
						 ,[DepartmentID]
						 ,[GroupID]
						 ,[ProgramID]
						 ,[TrainingStatusID]
						 ,[DateAdded]
						 ,[DateCompleted]
						 ,[TenantID]
						 )
			SELECT [e].[EmployeeID]
				,[e].[DepartmentID]
				,[g].[GroupID]
				,1122 AS [ProgramID]
				,1000 AS [TrainingStatusID]
				,GETDATE() AS [DateAdded]
				,NULL AS [DateCompleted]
				,[e].[TenantID]			
			FROM Inserted AS e
				JOIN [dbo].[Department] AS d
					ON [e].[DepartmentID] = [d].[DepartmentID]
				JOIN [dbo].[GroupDepartment] AS gd
					ON [d].[DepartmentID] = [gd].[DepartmentID]
				JOIN [dbo].[Group] AS g
					ON [gd].[GroupID] = [g].[GroupID]
						AND [g].[MangersSupervisors] = 1									
			INSERT INTO [dbo].[TrainingRoster]
						 (
						  [EmployeeID]
						 ,[DepartmentID]
						 ,[GroupID]
						 ,[ProgramID]
						 ,[TrainingStatusID]
						 ,[DateAdded]
						 ,[DateCompleted]
						 ,[TenantID]
						 )
			SELECT [e].[EmployeeID]
				,[e].[DepartmentID]
				,[g].[GroupID]
				,1111 AS [ProgramID]
				,1000 AS [TrainingStatusID]
				,GETDATE() AS [DateAdded]
				,NULL AS [DateCompleted]
				,[e].[TenantID]	
			FROM Inserted AS e
				JOIN [dbo].[Department] AS d
					ON [e].[DepartmentID] = [d].[DepartmentID]
				JOIN [dbo].[GroupDepartment] AS gd
					ON [d].[DepartmentID] = [gd].[DepartmentID]
				JOIN [dbo].[Group] AS g
					ON [gd].[GroupID] = [g].[GroupID]
						AND [g].[MangersSupervisors] = 1							
		END --IF @IsManager = 1
	END
END

-
Richard Ray
Jackson Hole Mountain Resort

 

[RRinTetons]

The insert code is

INSERT INTO [dbo].[Employee]
        (
         [FirstName]
        ,[LastName]
        ,[EmailAddress]
        ,[DepartmentID]
        ,[HireStatusID]
        ,[WorkTypeID]
        ,[ManagerSupervisor]
        ,[AddedToITS]
        ,[TenantID]
        )
SELECT  [is].[First]
       ,[is].[Last]
       ,[is].[EmailAddress]
       ,[is].[DeptID]
       ,[is].[HireStatusID]
       ,[is].[WorkTypeID]
       ,[is].[Mgr/Super]
       ,0
       ,1
FROM [dbo].[ImportStudent] AS [is]

where ImportStudent has several hundred rows in it. The error message is:

Msg 512, Level 16, State 1, Procedure trgMaintainRoster_INSERT, Line 27
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

The debugger shows the error happening on the line in the trigger that reads:

    DECLARE @HireStatus INT
    SET @HireStatus = (    SELECT [e].[HireStatusID]
                        FROM INSERTED AS [e]
                        )

PK on the Employee table is [Employee].[EmployeeID]

-
Richard Ray
Jackson Hole Mountain Resort

 

[bborissov]

Try this (NOT TESTED!!!!!!)

ALTER TRIGGER [dbo].[trgMaintainRoster_INSERT]
   ON  [dbo].[Employee]   
   AFTER INSERT   
AS
BEGIN
    SET NOCOUNT ON;
    --Employee is a New Hire or New Volunteer
    INSERT INTO [dbo].[TrainingRoster]
                             (
                              [EmployeeID]
                             ,[DepartmentID]
                             ,[GroupID]
                             ,[ProgramID]
                             ,[TrainingStatusID]
                             ,[DateAdded]
                             ,[DateCompleted]
                             ,[TenantID]
                             )
    SELECT [e].[EmployeeID]
          ,[e].[DepartmentID]
          ,[g].[GroupID]
          ,[p].[ProgramID]
          ,1000 AS [TrainingStatusID]
          ,GETDATE() AS [DateAdded]
          ,NULL AS [DateCompleted]
          ,[e].[TenantID]                    
     FROM Inserted AS e
          JOIN [dbo].[Department]      AS d  ON [e].[DepartmentID] = [d].[DepartmentID]
          JOIN [dbo].[GroupDepartment] AS gd ON [d].[DepartmentID] = [gd].[DepartmentID]
          JOIN [dbo].[Group]           AS g  ON [gd].[GroupID]     = [g].[GroupID]   AND [g].[MangersSupervisors] =  e.[ManagerSupervisor]
          JOIN [dbo].[GroupProgram]    AS gp ON [g].[GroupID]      = [gp].[GroupID] AND [gp].[ProgramID]         != 1122
          JOIN [dbo].[Program]         AS p  ON [gp].[ProgramID]   = [p].[ProgramID]      
     WHERE e.[HireStatusID] IN (1002, 1006)
     --Employee is a New Hire or New Volunteer
     

    --- Employee is a Returning Hire or Returning Volunteer
      INSERT INTO [dbo].[TrainingRoster]
                         (
                          [EmployeeID]
                         ,[DepartmentID]
                         ,[GroupID]
                         ,[ProgramID]
                         ,[TrainingStatusID]
                         ,[DateAdded]
                         ,[DateCompleted]
                         ,[TenantID]
                         )
      SELECT [e].[EmployeeID]
            ,[e].[DepartmentID]
            ,[g].[GroupID]
            ,1122 AS [ProgramID]
            ,1000 AS [TrainingStatusID]
            ,GETDATE() AS [DateAdded]
            ,NULL AS [DateCompleted]
            ,[e].[TenantID]
                    
       FROM Inserted AS e
            JOIN [dbo].[Department]      AS d  ON [e].[DepartmentID] = [d].[DepartmentID]
            JOIN [dbo].[GroupDepartment] AS gd ON [d].[DepartmentID] = [gd].[DepartmentID]
            JOIN [dbo].[Group]           AS g  ON [gd].[GroupID] = [g].[GroupID] AND [g].[MangersSupervisors] ==  e.[ManagerSupervisor]
     WHERE NOT e.[HireStatusID] IN (1002, 1006) --Employee is a Returning Hire or Returning Volunteer
     
     
      --- Employee is a Returning Hire or Returning Volunteer
      --- This is the second insert for Managers.
      INSERT INTO [dbo].[TrainingRoster]
                         (
                          [EmployeeID]
                         ,[DepartmentID]
                         ,[GroupID]
                         ,[ProgramID]
                         ,[TrainingStatusID]
                         ,[DateAdded]
                         ,[DateCompleted]
                         ,[TenantID]
                         )
      SELECT [e].[EmployeeID]
            ,[e].[DepartmentID]
            ,[g].[GroupID]
            ,1122 AS [ProgramID]
            ,1000 AS [TrainingStatusID]
            ,GETDATE() AS [DateAdded]
            ,NULL AS [DateCompleted]
            ,[e].[TenantID]
                    
       FROM Inserted AS e
            JOIN [dbo].[Department]      AS d  ON [e].[DepartmentID] = [d].[DepartmentID]
            JOIN [dbo].[GroupDepartment] AS gd ON [d].[DepartmentID] = [gd].[DepartmentID]
            JOIN [dbo].[Group]           AS g  ON [gd].[GroupID] = [g].[GroupID] AND [g].[MangersSupervisors] ==  e.[ManagerSupervisor]
     WHERE NOT e.[HireStatusID] IN (1002, 1006) 
           AND e.[ManagerSupervisor] = 1
      --- Employee is a Returning Hire or Returning Volunteer
      --- This is the second insert for Managers.
END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[RRinTetons]

Nicely done! I did make a few changes where some edits were incomplete but they were obvious in testing. In general it's short, sweet, and uses fewer weird conditionals. Thanks very much!!

-
Richard Ray
Jackson Hole Mountain Resort