[Original] [SQL Server] trigger to obtain information about insert, delete, and update rows

Example of a trigger codes:

 

Code of the insert, delete, and update trigger:
-- DBO. trgroups. SQL

If exists (
Select * From sysobjects where
Name = 'trgroups' and type = 'tr ')
Begin
Drop trigger trgroups
If not exists (
Select * From sysobjects where
Name = 'trgroups' and type = 'tr ')
Print 'drop trgroups succeeded'
Else
Print 'drop trgroups failed'
End
Else
Print 'attempting to create trgroupup'
Go

Create trigger DBO. trgroups
On groups
After insert, update, delete
As

-- This trigger submits a cached rates Delete job for modified groups.
-- It also updates the groups 'last modified columns for modified groups.
-- It also updates the groups 'State (if currently unspecified) based on
-- The (first) State extracted from groups 'zip code for new/modified groups.
-- No error checking is currently being completed MED.

Set nocount on -- stop display of rowcount messages

Declare
@ Groupid int,
@ Deletedcount int,
@ Returncode int

Select
@ Deletedcount = count (*)
From
Deleted with (nolock)

If @ deletedcount> 0
Begin
Declare groupidcursor cursor
Fast_forward
For
Select distinct
T1.groupid
From
(
Select
Groupid
From
Inserted with (nolock)

Union

Select
Groupid
From
Deleted with (nolock)
) As T1

Open groupidcursor

Fetch next
From
Groupidcursor
Into
@ Groupid

While @ fetch_status = 0
Begin
Exec @ returncode = DBO. spcachingmoduleaddjobdeletecachedratesbygroupid
@ Groupid = @ groupid

Fetch next
From
Groupidcursor
Into
@ Groupid
End

Close groupidcursor
Deallocate groupidcursor
End

Go

If exists (
Select * From sysobjects where
Name = 'trgroups' and type = 'tr ')
Print 'create trgroups succeeded'
Else
Print 'create trgroups failed'
Go

 

Two special tables are used in the trigger statement:DeletedTable andInsertedTable.
The deleted table is used to store copies of rows affected by the delete and update statements. When executing the delete or update statement, the row is deleted from the trigger table and transmitted to the deleted table. The deleted table and the trigger table do not have the same rows.

The inserted Table is used to store copies of rows affected by the insert and update statements. In an insert or update transaction, the new row is added to both the inserted Table and the trigger table. The rows in the inserted Table are copies of the new rows in the trigger table.

1. Insert)
The inserted Table has data, and the deleted table has no data.

2. Delete)
The inserted Table has no data, and the deleted table has data.

3. Update)
The inserted Table has data (new data), and the deleted table has data (old data)