Inserted和Deleted在insert、update、delete的简单使用
Inserted表和Deleted表,仅仅在触发器运行时存在。当insert、update、delete操作时,可使用借助两个表来输出(使用OUTPUT关键字)操作前后的数据的变化。
Insert | Update | Delete | |
Inserted | 插入后该行的数据 | 更新后该行的数据(新数据) | 不可用 |
Deleted | 不可用 | 更新前该行的数据(旧数据) | 删除前该行的数据 |
一、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@xxx.com')
insert into [HLLDb].[dbo].[Table_1] (name,email)
OUTPUT Inserted.name,Inserted.email
values ('b','b@xxx.com')
二、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@xxx.com'
OUTPUT Deleted.name as oldname ,Deleted.email as oldemail
,Inserted.name as [newname],Inserted.email as newemail
where name = 'a'
update [HLLDb].[dbo].[Table_1] set name = 'd', email = 'd@xxx.com'
OUTPUT Deleted.name as oldname ,Deleted.email as oldemail
,Inserted.name as [newname],Inserted.email as newemail
where 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 oldemail
where name in('c', 'd')
SELECT TOP 1000 [Id]
,[name]
,[email]
FROM [HLLDb].[dbo].[Table_1]
欢迎加群,日用儿童母婴分享大牌淘宝京东优惠券