Using SQL Server 2008 Express. Making a trigger to pull the employee# based on the EmployeeName.

I can't figure out why I'm getting the message that the subquery returned more than 1 value. Does it have something to do with nulls?

Here's my trigger:


USE [Personnel]
GO
/****** Object: Trigger [dbo].[GetEmpNo] Script Date: 02/08/2010 12:44:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[GetEmpNo]
ON [dbo].[Disciplinary_Action_Form]
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 @Name VARCHAR(50)
DECLARE @EEno VARCHAR(5)
DECLARE @FULL VARCHAR(50)

SELECT @Name = (Select Name from Inserted)
Select @Full = (Select "Full" from dbo.Employees$)
SELECT @EEno = (Select EmpNo from dbo.Employees$ where "Full" = @Name)

UPDATE dbo.Disciplinary_Action_Form
SET Name=@Name, EEno=@EEno
Where F_DocumentID = (select F_DocumentID from inserted)

END

So, how many records do you expect this to return:

Select "Full" from dbo.Employees$

Yep, that was it. I don't know why I had that in there. Always nice to have an extra set of eyes. Thanks!

Hi

Apart from the issue you have for multiple returned FULL, your trigger will also be in trouble if multiple rows inserted at the same time like below:

INSERT INTO [dbo].[Disciplinary_Action_Form]
SELECT top 100 *
FROM [dbo].[Other_Disciplinary_Action_Form]

Your trigger will only take care of the last row entered out of 100 rows.

You need to loop through all the rows in the INSERTED table as below:

DECLARE Disciplinary_Action_Form_Cursor CURSOR FOR
SELECT Name
FROM Inserted

OPEN Disciplinary_Action_Form_Cursor
FETCH NEXT FROM Disciplinary_Action_Form_Cursor INTO @Name

-- Loop through cursor until finished
WHILE @@FETCH_STATUS = 0
BEGIN

-- place your code here for after insert action


--Fetch_Next:
FETCH NEXT FROM Disciplinary_Action_Form_Cursor INTO @Name

END

This routine also should be applied to UPDATE and DELETE triggers as well.

Rather than the cursor (which is usually suspect for performance problems), you should probably join to the inserted table in this case. Instead of You might have
You will of course need to work EEno in there, but this should get you started.