none
INSERT EVENT TRIGGER

    Question

  • Hi All,

    I am trying to INSERT INTO base table so that my trigger returns the expected output. But when I do that I get error-

    Msg 8152, Level 16, State 13, Procedure tr_tblemployee_ForInsert, Line 10
    String or binary data would be truncated.
    The statement has been terminated.

    I am using:

    CREATE TRIGGER tr_tblemployee_ForInsert
    on tblemployee
    FOR INSERT
    AS
    BEGIN
        DECLARE @EmployeeID Int
        SELECT @EmployeeID = EmployeeID FROM INSERTED

    INSERT INTO tblEmployAudit VALUES (
     'New Employee with ID =' +
       CAST(@EMPLOYEEID as NVARCHAR(20)) +
     'added at' +
       CAST(GETDATE() as NVARCHAR(20))
    )
    END

    The tblemployee table has EmployeeID as INT type and the tblEmployAudit  table has EmployeeID as Primary key INT type.

    Please advise.

    Best R.

    Sk

    Tuesday, April 08, 2014 5:38 AM

Answers

  • This is clear indication that your are trying to insert larger value to a table which is more than the column can accommodate.

    Try the below to understand:

    create Table tblEmployAudit (Col1 nvarchar(20))
    Insert into tblEmployAudit values('New Employee with ID =' + 
       CAST(12345 as NVARCHAR(20)) +
     'added at' +
       CAST(GETDATE() as NVARCHAR(20)))
       
    Select * From tblEmployAudit
    
    Drop table tblEmployAudit

    Tuesday, April 08, 2014 5:50 AM
  • There are three obvious problems here:

        DECLARE @EmployeeID Int
        SELECT @EmployeeID = EmployeeID FROM INSERTED

    A trigger fires once per statement, so inserted can contain multiple rows. Thus, reading one of these many rows into a variable is not a good idea.

    INSERT INTO tblEmployAudit VALUES (

    Always -  and I mean always! - include an explicit list of columns to insert to. If the DBA innocently adds a nullable column to the table, your trigger will start failing.

    And then it was the error message:

    Msg 8152, Level 16, State 13, Procedure tr_tblemployee_ForInsert, Line 10
    String or binary data would be truncated.
    The statement has been terminated.

    So this message can be nuisance, because it may be difficult to find the troublesome string. However, in this case it's fairly straightforward: you need to enlarge the column in the audit table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 08, 2014 7:22 AM

All replies

  • >>String or binary data would be truncated.


    This error indicates that you are inserting a value that is  larger than the column size defined in the table.

    Check the tblEmployAudit column sizes and the length of the string you are inserting.


    Satheesh
    My Blog | How to ask questions in technical forum



    Tuesday, April 08, 2014 5:40 AM
  • This is clear indication that your are trying to insert larger value to a table which is more than the column can accommodate.

    Try the below to understand:

    create Table tblEmployAudit (Col1 nvarchar(20))
    Insert into tblEmployAudit values('New Employee with ID =' + 
       CAST(12345 as NVARCHAR(20)) +
     'added at' +
       CAST(GETDATE() as NVARCHAR(20)))
       
    Select * From tblEmployAudit
    
    Drop table tblEmployAudit

    Tuesday, April 08, 2014 5:50 AM
  • There are three obvious problems here:

        DECLARE @EmployeeID Int
        SELECT @EmployeeID = EmployeeID FROM INSERTED

    A trigger fires once per statement, so inserted can contain multiple rows. Thus, reading one of these many rows into a variable is not a good idea.

    INSERT INTO tblEmployAudit VALUES (

    Always -  and I mean always! - include an explicit list of columns to insert to. If the DBA innocently adds a nullable column to the table, your trigger will start failing.

    And then it was the error message:

    Msg 8152, Level 16, State 13, Procedure tr_tblemployee_ForInsert, Line 10
    String or binary data would be truncated.
    The statement has been terminated.

    So this message can be nuisance, because it may be difficult to find the troublesome string. However, in this case it's fairly straightforward: you need to enlarge the column in the audit table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, April 08, 2014 7:22 AM