locked
SQL Server After Insert trigger problem RRS feed

  • 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

    Sunday, September 6, 2015 9:53 AM

Answers

  • 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

    • Marked as answer by Naomi N Monday, September 7, 2015 3:25 AM
    Sunday, September 6, 2015 11:11 AM

All replies

  • 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

    • Marked as answer by Naomi N Monday, September 7, 2015 3:25 AM
    Sunday, September 6, 2015 11:11 AM
  • Visakh:

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

    Sincerely,

    Bosco

    • Marked as answer by bosco dog Sunday, September 6, 2015 5:31 PM
    • Unmarked as answer by Naomi N Monday, September 7, 2015 3:25 AM
    Sunday, September 6, 2015 5:31 PM