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’.
- Inserted – The 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