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.

Heres 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),

);

Heres 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)

);

Heres 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

Heres 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

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

Visakh:

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

Sincerely,

Bosco

• Marked as answer by bosco dog 13 hours 33 minutes ago
• Unmarked as answer by Naomi NModerator 3 hours 39 minutes ago