比较SQL SERVER中的问题 [英] Compare problem in SQL SERVER

问题描述

大家好.我能回答一个问题吗?我已经创建了一个用于更新的触发器，使用它我想列出已更新的列，但是在更新存储过程中，我会更新所有列，因此我只想获取更新后包含不同数据的列
例如，获取包含不同数据的列(从插入比较到删除)(在触发器上)
非常感谢

Hi to every one. could I have a answer about a problem. i have created a trigger FOR update, using it i want to list the columns that has been updated but on update stored procedure I update all columns so I want to get just the columns that contain different data after update
For examples GET COLUMNS THAT CONTAINS DIFFERENT DATA FROM INSERTED COMPARE TO DELETED (On TRIGGER)
thanks a lot

推荐答案

朋友您好，

我找到了使用更新后触发器的解决方案.
只需假设有一个具有以下列(Eid，Name，Salary)的Employee表
Eid是主键..因此无法更改.

我的触发器将显示所有已更新的列.
我已经使用了2个系统虚拟表(已插入和删除).
我知道表中存在所有列，要为表创建触发器，我直接使用列名.

代码:-

Hi friend,

I have found a solution using After Update Trigger.
Just assume am having a table Employee with the following columns(Eid,Name,Salary)
Eid is primary key.. so It can''t be changed.

My trigger will show what are all the columns are updated.
I have used 2 System virtual tables(inserted and deleted).
As I know what are all the columns are existed in my table,to create a trigger for my table, I just use column names directly.

Code:-

Alter trigger Tr_UpdateEmp
On Employee
After Update
As
Begin
declare @Eid int,@name varchar(20),@Salary money,@Eid1 int,@name1 varchar(20),@Salary1 money,@reuslt varchar(20),@reuslt1 varchar(20),@reuslt2 varchar(20)
Set @Eid=0
set @name=''
set @Salary=0
Set @Eid1=0
set @name1=''
set @Salary1=0
set @reuslt=''
set @reuslt1=''
set @reuslt2=''


select @Eid=Eid,@name=name,@Salary=Salary from Inserted
Select @Eid1=Eid,@name1=name,@Salary1=Salary from Deleted
select @reuslt1=case when @name<>@name1 Then 'Name' End
select @reuslt2=case when @Salary<>@Salary1 Then 'Salary' End

IF @reuslt1 <>'' and @reuslt2<>''
Begin
Select Name,Salary from Employee where Eid=@Eid
End
Else IF @reuslt1 ='' and @reuslt2<>''
Begin
Select Salary from Employee where Eid=@Eid
END
Else IF @reuslt1 <>'' and @reuslt2=''
Begin
Select Name from Employee where Eid=@Eid
END
END

-请保持联系.

阿拉文斯·G
SQL Developer，
Kadamba Technologies Pvt.Ltd，
钦奈
电子邮件:-[已删除]//不要在此处提及您的邮件ID

--Please keep me in touch.

Aravinth.G
SQL Developer,
Kadamba Technologies Pvt.Ltd,
Chennai
Email:- [Deleted] // Don''t mention your mail id here

请参阅 COLUMNS_UPDATED() [

See UPDATE(COLUMN)[^] and
COLUMNS_UPDATED ( )[^]

这篇关于比较SQL SERVER中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！