0

I have a table for which a update stored procedure has been created. For this table I created an update trigger. But when I either execute the stored procedure or execute an update query I am getting this error message:

Msg 8152, Level 16, State 14, Procedure OperationHistory, Line 95 String or binary data would be truncated. The statement has been terminated.

Create Trigger OperationHistory on [dbo].[Employee]
For Update
AS
Declare @EmployeeID int;
Declare @Employeename varchar(100);
Declare @SSN bigint;
Declare @Phonenumber bigint;
Declare @JoinDate Date;
Declare @OperationHistory varchar(100);
Declare @Operationtimestamp Datetime;

select @EmployeeID=i.EmployeeId from inserted i;    
select @Employeename=i.Employeename from inserted i;    
select @SSN=i.SSN from inserted i;
select @Phonenumber=i.Phonenumber from inserted i;  
select @JoinDate=i.JoinDate from inserted i;    

If Update (EmployeeId)
Set @OperationHistory='updated employee id';
If Update (Phonenumber)
Set @OperationHistory='updated phonenumber';
If Update (SSN)
Set @OperationHistory='updated SSN';
If Update (Employeename)
Set @OperationHistory='updated employeename';
If Update (JoinDate)
Set @OperationHistory='updated JoineDate';

Insert into Employee_History
Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Go
  • 1
    This means what it says, somewhere in your insert, data is being truncated. Unfortunately, SQL won't give you any help figuring out which fields are throwing the error. There's a connect item open for this behavior. connect.microsoft.com/SQLServer/feedback/details/339410/…. You'll need to do it the old fashioned way. Find out what data is being inserted and then narrow it down from there. – Kris Gruttemeyer Jun 5 '15 at 15:56
  • 1
    This trigger will not handle a multi-row update and return values for each row. It also will not return a value inserting accurately every column that was updated if more than one column changed. Is it really impossible for multiple rows and columns to get updated in a single operation? What is the trigger trying to accomplish? Remember that a trigger handles every row changed in the insert, update, or delete "all at once". – RLF Jun 5 '15 at 18:16
1

Insert into Employee_History Values(@EmployeeID,@Employeename,@SSN,@Phonenumber,@JoinDate,@OperationHistory,getdate())

Make sure the column order of the Employee_History table matches the order of the variables listed. The best practice is to list column names on INSERT statements.

The other possible cause is that the EmployeeName column in the Employee_History is shorter than 100 characters.

Importantly, as suggested in the comments, triggers fire once per statement instead of once per row so must be coded to handle multi-row inserts. The history table insert should be refactored as INSERT INTO Employee_History(column-list) SELECT column-list FROM inserted Not related to this error, though.

Also, there are a number of other errors in the trigger. An UPDATE statement can update multiple columns yet the trigger code identifies only one the potentially changed columns. The UPDATED function in an UPDATE trigger does not indicate the value has changed, only that it specified in the SET clause of the UPDATE statement. This may cause erroneous messages in the history table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.