Inserted和Deleted在insert、update、delete的简单使用

﹏ヽ暗。殇╰゛Y 2022-01-30 05:45 427阅读 0赞

Inserted表和Deleted表,仅仅在触发器运行时存在。当insert、update、delete操作时，可使用借助两个表来输出（使用OUTPUT关键字）操作前后的数据的变化。

<table style="width:700px;"> 
 <tbody> 
  <tr> 
   <td></td> 
   <td> <h2><span style="color:#3399ea;">Insert</span></h2> </td> 
   <td> <h2><span style="color:#3399ea;">Update</span></h2> </td> 
   <td> <h2><span style="color:#3399ea;">Delete</span></h2> </td> 
  </tr> 
  <tr> 
   <td> <h2><span style="color:#3399ea;">Inserted</span></h2> </td> 
   <td>插入后该行的数据</td> 
   <td>更新后该行的数据（新数据）</td> 
   <td>不可用</td> 
  </tr> 
  <tr> 
   <td> <h2><span style="color:#3399ea;">Deleted</span></h2> </td> 
   <td>不可用</td> 
   <td>更新前该行的数据（旧数据）</td> 
   <td>删除前该行的数据</td> 
  </tr> 
 </tbody> 
</table>

###  ###

### **一、insert语句** ###

1) Inserted表:存放着插入后该行的数据  
2) Deleted表状态为不可用

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70][] ###

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表:存放着更新前该行的数据（旧数据）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70 1][]

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表:存放着删除前该行的数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70 2][]

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]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70 3][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70]: /images/20220130/e3d67114ba8e4c9781d725114ec8438f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70 1]: /images/20220130/2e51258d91494fdb986994b0c5cfb8e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70 2]: /images/20220130/7bc91119aa2e44f293e7c68e0e54ee8d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NpbmF0XzE2OTk4OTQ1_size_16_color_FFFFFF_t_70 3]: /images/20220130/f9657c18269840f0982a8bee42fea9ee.png