Triggers- DML - after triggers(for)

 There are 4 types of triggers. they are :

1. DML triggers

2.DDL triggers.

3. CLR triggers.

4. Logon Triggers

1. DML triggers:

  Triggers fired inresponse of DML events (insert, update, delete).

It can also be classified into two types:

 1. After Triggers (also known as FOR triggers.)

 2. Instead of Triggers

1.After Triggers

Triggers fired after insert update or delete events.

1. After Insert trigger (example):

alter trigger tr_Employee on Employee for Insert

as Begin

Declare

 @id int

 

  set @id= (select Id from Inserted) --id column from the inserted row which WAS inserting

insert into HousePartyLog select 'House party candidate added with id' + cast(@id as varchar(50)) + cast(getdate() as varchar(max))

end

Inserted table is auto created table while insert operation is carried out by trigger. this table records recently inserted rows when the trigger is fired.

 2. After delete trigger

It also maintains an auto generated table named "Deleted" table. 

We can simply do anything when delete is fired in particular table.

USE [testDatabase]

GO

/****** Object:  Trigger [dbo].[tr_Employee_deleted]    Script Date: 02/11/2020 5:07:09 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[tr_Employee_deleted] ON [dbo].[Employee] FOR DELETE

AS BEGIN

DECLARE 

@ID INT 

 select @ID= Id FROM deleted

INSERT INTO dbo.HousePartyLog

        ( Info )

VALUES  ( CAST(@ID AS varchar(10)) + 'Is deleted at' + CAST(GETDATE() AS varchar(max))) -- Info - varchar(max)

         

END

3. after update Triggers

Unlike insert and delete trigger,it doent generate update table since update is a combination of both insert and delete, therefore update creates both insert and delete table and we can use them as how we like.

USE [testDatabase]

GO

/****** Object:  Trigger [dbo].[tr_employee_up]    Script Date: 02/11/2020 3:08:40 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER trigger [dbo].[tr_employee_up]

on [dbo].[Employee]

for Update

as

Begin

      -- Declare variables to hold old and updated data

      Declare @Id int

      Declare @OldName nvarchar(20), @NewName nvarchar(20)

      Declare @NewEmployeeId int, @OldEmployeeId int

      -- Variable to build the audit string

      Declare @AuditString nvarchar(1000)

      

      -- Load the updated records into temporary table

      Select *

      into #TempTable

      from inserted

     Select *

      into #TempTable2

      from deleted

     

      -- Loop thru the records in temp table

     -- While(Exists(Select Id from #TempTable))

      Begin

            --Initialize the audit string to empty string

            Set @AuditString = ''

           

            -- Select first row data from temp table

            Select Top 1 @Id = Id, @NewName = Name, 

            @NewEmployeeId = EmployeeId from #TempTable

           

            -- Select the corresponding row from deleted table

            Select top 1 @OldName = Name, @OldEmployeeId = EmployeeId from #TempTable2 

   

     -- Build the audit string dynamically           

            Set @AuditString = 'Employee with Id = ' + Cast(@Id as nvarchar(4)) + ' changed'

            if(@OldName <> @NewName)

                  Set @AuditString = @AuditString + ' NAME from ' + @OldName + ' to ' + @NewName

            if(@OldEmployeeId <> @NewEmployeeId)

                  Set @AuditString = @AuditString + ' employeeid from ' + cast(@OldEmployeeId as varchar(max)) + ' to ' + cast(@NewEmployeeId as varchar(max))

                 

           

            insert into HousePartyLog values(@AuditString)

            

            -- Delete the row from temp table, so we can move to the next row

            Delete from #TempTable where Id = @Id

     truncate table #TempTable2 

      End

End