Inserted和Deleted在insert、update、delete的简单使用
Inserted表和Deleted表,仅仅在触发器运行时存在。当insert、update、delete操作时，可使用借助两个表来输出（使用OUTPUT关键字）操作前后的数据的变化。
一、insert语句
1) Inserted表:存放着插入后该行的数据
2) Deleted表状态为不可用
SELECT TOP 1000 [Id],[name],[email]FROM [HLLDb].[dbo].[Table_1]--truncate table [HLLDb].[dbo].[Table_1]insert into [HLLDb].[dbo].[Table_1] (name,email) OUTPUT Inserted.name ,Inserted.email values ('a','a@')insert into [HLLDb].[dbo].[Table_1] (name,email) OUTPUT Inserted.name,Inserted.email values ('b','b@')
二、update语句
1) Inserted表:存放着更新后该行的数据（新数据）
2) Deleted表:存放着更新前该行的数据（旧数据）
SELECT TOP 1000 [Id],[name],[email]FROM [HLLDb].[dbo].[Table_1]update [HLLDb].[dbo].[Table_1] set name = 'c', email = 'c@'OUTPUT Deleted.name as oldname ,Deleted.email as oldemail,Inserted.name as [newname],Inserted.email as newemailwhere name = 'a'update [HLLDb].[dbo].[Table_1] set name = 'd', email = 'd@'OUTPUT Deleted.name as oldname ,Deleted.email as oldemail,Inserted.name as [newname],Inserted.email as newemailwhere name = 'b'
三、delete语句
1) Inserted表状态为不可用
2) Deleted表:存放着删除前该行的数据
SELECT TOP 1000 [Id],[name],[email]FROM [HLLDb].[dbo].[Table_1]delete [HLLDb].[dbo].[Table_1] OUTPUT Deleted.name as oldname ,Deleted.email as oldemailwhere name in('c', 'd')SELECT TOP 1000 [Id],[name],[email]FROM [HLLDb].[dbo].[Table_1]
欢迎加群，日用儿童母婴分享大牌淘宝京东优惠券
