Inserted and Deleted Simple uses of INSERT, update, and detele

Inserted and Deleted tables, which exist only while the trigger is running. When performing insert, update, and detele operations, you can use two tables to OUTPUT (using the OUTPUT keyword) changes to the data before and after the operation.

  ## Insert ## Update ## Detele
## Inserted Insert row data Updated data of the row (new data) Do not use
## Deleted Do not use Data of the row before update (old data) Delete the data in the previous row

 

 

 

 

 

 

Insert statement

[Deleted] the Deleted table was in an unavailable state

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'.'[email protected]')

insert into [HLLDb].[dbo].[Table_1] (name,email) 
OUTPUT Inserted.name,Inserted.email 
values ('b'.'[email protected]')
Copy the code

Update statement

[Deleted] The Inserted table contains the rows that were Deleted before the update.

SELECT TOP 1000 [Id]
      ,[name]
      ,[email]
  FROM [HLLDb].[dbo].[Table_1]


update [HLLDb].[dbo].[Table_1] set name = 'c', email = '[email protected]'
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 = '[email protected]'
OUTPUT Deleted.name as oldname ,Deleted.email as oldemail
,Inserted.name as [newname],Inserted.email as newemail
where name = 'b'
Copy the code

 

Detele statement

Inserted table 1) Unavailable 2) Deleted Table: Stores data in the row before deletion

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]
Copy the code