SQL Server After Insert trigger problem

Question

Hi: 

I am having problems with the trigger after insert procedure. I am getting this error:

Msg 512, Level 16, State 1, Procedure trg_setEmployeeIDonTables, Line 57

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.

Here’s my EMPLOYEE table

CREATE TABLE EMPLOYEE(

EMP_ID_NUM                 INTEGER PRIMARY KEY,

EMP_LNAME                  VARCHAR(40) NOT NULL,

EMP_FNAME                  VARCHAR(40) NOT NULL,

EMP_INITIAL                VARCHAR(10),

EMP_DOB                    DATETIME2(0) NOT NULL,

DATE_HIRED                 DATETIME2(0) NOT NULL,

STAFF_DATE                 DATETIME2(0),

RACE_ID                    INTEGER,

GENDER_ID                  INTEGER

FOREIGN KEY(RACE_ID) REFERENCES ETHNICITY(RACE_ID),

FOREIGN KEY(GENDER_ID) REFERENCES GENDER(GENDER_ID),

);

Here’s my PHONE_INFO table

CREATE TABLE PHONE_INFO(

EMP_ID_NUM                 INTEGER NOT NULL,

PHONE_TYPE_ID       INTEGER NOT NULL,

PHONE_NUMBER_ID            VARCHAR(10),

AREACODE            VARCHAR(3),

PHONE_EXCH          VARCHAR(3),

PHONE               VARCHAR(5),

DATE_ENTERED DATETIME2

PRIMARY KEY(EMP_ID_NUM, PHONE_TYPE_ID, PHONE_NUMBER_ID),

FOREIGN KEY(EMP_ID_NUM) REFERENCES EMPLOYEE(EMP_ID_NUM),

FOREIGN KEY(PHONE_TYPE_ID) REFERENCES PHONE_TYPE(PHONE_TYPE_ID)

);

Here’s my INSERT procedure

DROP PROCEDURE proc_insertEmployeePersonalData

GO

CREATE PROCEDURE proc_insertEmployeePersonalData

@empID              INTEGER,

@lastName           VARCHAR(40),

@firstName          VARCHAR(40),

@middleName         VARCHAR(40),

@dateBirth          DATETIME2(0),

@dateHired          DATETIME2(0),

@staffDate          DATETIME2(0),

@raceID                    INTEGER,

@genderID           INTEGER

AS

BEGIN

       IF     @dateHired is null

             SET @dateHired =  COALESCE(@dateHired, GETDATE())

             -- Check to see if the EMP_ID_NUM exists.  If not, then insert new employee data.

             IF NOT EXISTS(

                    SELECT EMP_ID_NUM

                    FROM EMPLOYEE

                    WHERE EMP_ID_NUM = @EmpID

                    )

             BEGIN

             INSERT INTO EMPLOYEE(EMP_ID_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB, DATE_HIRED, STAFF_DATE,

                    RACE_ID, GENDER_ID)

             VALUES(@empID, @lastName, @firstName, @middleName, @dateBirth, @dateBirth, @staffDate, @raceID, @genderID);

             SELECT EMP_ID_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB, DATE_HIRED, STAFF_DATE, RACE_ID, GENDER_ID

                    FROM EMPLOYEE

                    WHERE EMP_ID_NUM = @empID;

             END

                    ELSE

             BEGIN

                    -- An employee with the entered EMP_ID_NUM exists. Show the actual Employee that has that EMP_ID_NUM

                    SELECT EMP_ID_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB, DATE_HIRED, STAFF_DATE, RACE_ID, GENDER_ID

                    FROM EMPLOYEE

                    WHERE EMP_ID_NUM = @empID;

           END

            

END

GO

Here’s my trigger after insert

DROP TRIGGER trg_setEmployeeIDonTables

GO

CREATE TRIGGER trg_setEmployeeIDonTables

ON dbo.EMPLOYEE

AFTER INSERT AS

BEGIN

SET NOCOUNT ON

DECLARE @empID INTEGER

SET @empID = (SELECT EMP_ID_NUM FROM EMPLOYEE);

INSERT INTO PHONE_INFO (EMP_ID_NUM, PHONE_TYPE_ID, PHONE_NUMBER_ID, AREACODE, PHONE_EXCH, PHONE, DATE_ENTERED)

VALUES (@empID, 200, null, null, null, null, null);

INSERT INTO EMAIL_INFO(EMP_ID_NUM, EMAIL_TYPE_ID,  EMAIL_ADDRESS, DATE_ENTERED)

VALUES(@empID, 601,  null, null);

END

GO

Here's a sample insert statement:

EXEC proc_insertEmployeePersonalData @empID = 100037, @lastName = 'Jones', @firstName = 'Janet', @middleName = 'Beth', @dateBirth = '03-Jan-1994', @dateHired = '03-Mar-2010', @staffDate = '03-Mar-2010', @raceID = 501, @genderID = 1000;

Any help would be greatly appreciated.  Thank you for your time.

Bosco

Answer 1

The trigger currently you posted has below line

SET @empID = (SELECT EMP_ID_NUM FROM EMPLOYEE);

which assumes you'll have only single row in EMPLOYEE table always which is not true. So you need to rewrite it to accept all values and you need to use INSERTED table if you want to consider only recently inserted data

ie like

CREATE TRIGGER trg_setEmployeeIDonTables
ON dbo.EMPLOYEE
AFTER INSERT AS
BEGIN
SET NOCOUNT ON

INSERT INTO PHONE_INFO (EMP_ID_NUM, PHONE_TYPE_ID, PHONE_NUMBER_ID, AREACODE, PHONE_EXCH, PHONE, DATE_ENTERED)
SELECT EMP_ID_NUM, 200, null, null, null, null, null FROM INSERTED
INSERT INTO EMAIL_INFO(EMP_ID_NUM, EMAIL_TYPE_ID,  EMAIL_ADDRESS, DATE_ENTERED)
SELECT @empID, 601,  null, null FROM INSERTED
END
GO

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 2

Visakh:

That worked.  Thank you very much. Have a wonderful day.

Sincerely,

Bosco