是否可以在插入之前和使用触发器完成更新之后检索表记录 [英] Is it possible to retrieve table records both before insert and after update done using triggers

问题描述

嗨..
我使用触发器，例如，我有一个带有EmpId和EmpName的表EmployeeDetails.我已经在其中插入了2条记录，分别是"01，Kamal和02，Surya".通过使用AfterUpdate触发器，将第二个员工名称更新为"Raj".现在如何在更新表之前和之后都检索EmployeeDetails的数据，即我希望在同一张表中，更新名称之前的详细信息应为"Surya"，更新名称之后的应为"Raj" ...指导我很快

Hi..
Im using triggers,for example im having a table EmployeeDetails with EmpId and EmpName. I have already inserted 2 records into it called "01,Kamal and 02,Surya". By using AfterUpdate trigger,im updating the 2nd employee name as "Raj". Now how to retrieve the both the data of EmployeeDetails before and after updation of a table ie i want details as before updation name should be as "Surya" and after updation name should be as "Raj" in a same table...guide me soon

推荐答案

创建两个表.

Create Both tables.

 Create table Emp (
EmpId  varchar(5),
EmpName varchar(40)
)
Create table EmployeeDetailsHist (
EmpId  varchar(20)
, EmpName varchar(40)
, HistoryDate datetime 
) 

更新触发器后

After update Trigger

create TRIGGER EmpAfterUpdate
ON Emp
AFTER UPDATE
AS
IF ( UPDATE (EmpId) OR UPDATE (EmpName) )
BEGIN
INSERT INTO EmployeeDetailsHist (EmpId, EmpName, HistoryDate)
select EmpID, EmpName, getdate()
from deleted ;
END;

现有数据

1个Kamal
2 surya

Existing data

1 Kamal
2 surya

Update Emp set EmpName = ''Raj'' where EmpId = ''1''

select 
''Current'' as Origin, EmpId, EmpName from Emp 
union all
select ''History'' as Origin, EmpId, EmpName from EmployeeDetailsHist

结果是:

Result is:

Status    EmpId   Name 
------------------------
Current	1	Raj
Current	2	Surya
History	1	Kamal

没有类似的选项，您必须制作2条select语句以显示更新的数据和未更新的数据.萨钦先生的举止得体而轻松.例如

No options like that, You have to craft 2 select statement to show the updated data and unupdated data. Mr Sachin have give easy and good eg.

这篇关于是否可以在插入之前和使用触发器完成更新之后检索表记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！