Sql Triggers-Implementation Issue

Hi guys i am making 2 tables for implementing triggers--

create table emp(id int primary key identity(1,1),
name char(40),
salary varchar(50),
gender char(40),
departmentid int);

insert into emp(name,salary,gender,departmentid) values ('jimmi',4800,'Male',4);

create table emp_audit
(
id int primary key identity(1,1),
audit varchar(60)
);
alter trigger trigger_em_update on emp for update
as begin
Declare @Id int
Declare @oldname char(40),@newname char(40)
Declare @oldsal int,@newsal int
Declare @oldgen char(40),@newgen char(40)
Declare @olddeptid int,@newdeptid int

Declare @auditstring nvarchar(max)


--select * from deleted;

select * into #temptable from inserted;

while(Exists(select id from #temptable)) --boolean condition if there are rows are not
Begin
set @auditstring =''

--if there are many rows we still select the first one

select Top 1 @Id =id,@newname=name,@newgen=gender,@newsal=salary,@newdeptid=departmentid
from #temptable;

select Top 1 @Id =id,@oldname=name,@oldgen=gender,@oldsal=salary,@olddeptid=departmentid
from deleted where @Id=id;

set @auditstring=' Employee with id= '+CAST(@Id as varchar(20))+ ' changed '
if(@oldname<>@newname)
set @auditstring=@auditstring + 'name from '+ @oldname +' to' +@newname

if(@oldsal<>@newsal)
set @auditstring=@auditstring + ' salary from '+ @oldsal +' to ' +@newsal

if(@oldgen<>@newgen)
set @auditstring=@auditstring + ' gender from ' + @oldgen + ' to ' + @newgen

-- if(@olddeptid<>@newdeptid)
--set @auditstring=@auditstring + ' departmentid from ' + cast(@olddeptid as nvarchar(5))+' to '

insert into emp_audit values(@auditstring)

delete from #temptable where id=@Id

end
end
when i use update query
update emp set name='vishi',gender='male',salary='4000',departmentid=3 where id=3;


It gives an error

"Conversion failed when converting the nvarchar value ' Employee with id= 3 changed name from james tovishi salary from ' to data type int.
"
i don't know how to solve this..can you solve this..

emp

salary varchar(50)

Declare @oldsal int,@newsal int

from 'Foo ' to 'Foo'

3 Answers
3

The problem is in line:

if(@oldsal<>@newsal)
set @auditstring=@auditstring + ' salary from '+ @oldsal +' to ' +@newsal


Should be:

if(@oldsal<>@newsal)
set @auditstring=@auditstring+ ' salary from '+CAST(@oldsal AS NVARCHAR(100))
+' to ' +CAST(@newsal AS NVARCHAR(100))


A couple of thoughts:

@oldsal<>@newsal

NULL

NULL

set @auditstring=@auditstring +....

set @auditstring += ...

NULL

NULL

CHAR(size)

VARCHAR(size)

inserted

deleted

Id

CASE

FOR XML

alter trigger trigger_em_update on emp for update
as begin
Declare @Id int
Declare @oldname varchar(40),@newname varchar(40)
Declare @oldsal int,@newsal int
Declare @oldgen varchar(40),@newgen varchar(40)
Declare @olddeptid int,@newdeptid int

Declare @auditstring nvarchar(max)


--select * from deleted;

select * into #temptable from inserted;

while(Exists(select id from #temptable)) --boolean condition if there are rows are not
Begin
set @auditstring =''

--if there are many rows we still select the first one

select Top 1 @Id =id,@newname=name,@newgen=gender,@newsal=salary,@newdeptid=departmentid
from #temptable;

select Top 1 @Id =id,@oldname=name,@oldgen=gender,@oldsal=salary,@olddeptid=departmentid
from deleted where @Id=id;

set @auditstring=' Employee with id= '+CAST(@Id as varchar(20))+ ' changed '
if(@oldname<>@newname)
set @auditstring+= 'name from '+ @oldname +' to' +@newname

if(@oldsal<>@newsal)
set @auditstring+= 'salary from ' +CAST(@oldsal AS NVARCHAR(100)) + ' to ' +CAST(@newsal AS NVARCHAR(100))

if(@oldgen<>@newgen)
set @auditstring+= 'gender from ' + @oldgen + ' to ' + @newgen

if(@olddeptid<>@newdeptid)
set @auditstring+= 'departmentid from ' + cast(@olddeptid as nvarchar(5))+' to'+cast(@newdeptid as nvarchar(5))

insert into emp_audit values(@auditstring)

delete from #temptable where id=@Id

end


end
when i execute this it gives an error"String or binary data would be truncated" where i did a mistake i don't know please refer the attached screenshot enter image description here

The problem is that one of the columns used with + is a number, probably salary. But your trigger is way too complicated.

+

SQL is a set-based language. So use the sets when you can:

alter trigger trigger_em_update on emp for update
as
begin
insert into emp_audit (auditstring) -- guessing the name
select ('Employee with id k= ' + CAST(@Id as varchar(20))+ ' changed ' +
(case when @oldname <> @newname
then 'name from '+ @oldname +' to ' + @newname + ' '
else ''
end) +
(case when @oldsal <> @oldsal
then 'salary from '+ convert(varchar(255), @oldsal) +' to ' + concvert(varchar(255), @newsal) + ' '
else ''
end) +
(case when @oldgen <> @newgen
then 'gender from '+ convert(varchar(255), @oldgen) +' to ' + concvert(varchar(255), @newgen) + ' '
else ''
end) +
(case when @olddeptid <> @newdeptid
then 'departmentid from '+ convert(varchar(255), @olddeptid) +' to ' + concvert(varchar(255), @newdeptid) + ' '
else ''
end)
) as auditstring
from inserted i join
deleted d
on i.id = d.id;
end;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.