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