Tags

, , ,

Hello ,

For someone new, I would just like to share the format of writing Triggers which I recently learned. While writing Triggers we use the tables ‘Inserted’ and ‘Deleted’.

  • InsertedThe inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table.
  • Deleted The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table.

1 more fact which i would like to make clear is :

  • An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table. 

Consequently, we can find the difference between the state of a table before and after a data modification and take actions based on that difference.

In my scenario, i needed to update the field “IsRepeatCall” on IncedentExtensionBase table when the difference between any two cases with similar product were logged between 5 days.

The Code goes as below:

CREATE TRIGGER [dbo].[repeatcall]
 ON [dbo].[IncidentExtensionBase]
 AFTER INSERT,UPDATE
 AS 
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 declare @newdate datetime
 declare @olddate datetime
 declare @oldguid varchar(50)
 declare @newguid varchar(50)
 declare @productid varchar(50)
 declare @isrepeat bit
 
 select @newdate=d.CreatedOn , @newguid=d.IncidentId,@productid=e.new_ProductInstallationId from IncidentBase d,inserted e where d.IncidentId=e.IncidentId and d.CaseTypeCode=2
 
 select top 1 @olddate=b.CreatedOn, @oldguid=b.IncidentId,@isrepeat=a.new_IsRepeatCall from IncidentExtensionBase a, IncidentBase b, inserted c

 where a.IncidentId=b.IncidentId and b.CreatedOn < @newdate and
 CaseTypeCode=2 and StateCode<>2 and a.new_ProductInstallationId= c.new_ProductInstallationId
 order by b.CreatedOn desc
 
if @olddate is not null and @productid is not null and @isrepeat=0
 begin
if datediff(day,@olddate,@newdate) <=5
 begin
 update IncidentExtensionBase set new_IsRepeatCall=1
 where IncidentId=@oldguid
 update IncidentExtensionBase set new_IsRepeatCall=1
 where IncidentId=@newguid
 
 end
 end
 
 END

Thanks !!!

🙂