Sql-server – The statement has been terminated

sql servertrigger

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

Best Answer

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.

Related Question