ALTER TRIGGER [dbo].[TriggerAfterUpdateTicketMaster] ON [dbo].[TicketMaster]
AFTER UPDATE
AS
BEGIN TRANSACTION
BEGIN TRY
SET NOCOUNT ON;
DECLARE @TicketID BIGINT,@CompanyCode NVARCHAR(30),@NewModifiedBy NVARCHAR(50)
DECLARE @LogDesc NVARCHAR(MAX)
SET @LogDesc='';
SELECT @TicketID=i.TicketID FROM inserted i;
SELECT @CompanyCode=i.[CompanyCode] FROM deleted i;
SELECT @NewModifiedBy=i.ModifiedBy FROM inserted i;
IF UPDATE(DueDate)
BEGIN
DECLARE @NewDueDate DATETIME,@OldDueDate Datetime
SELECT @OldDueDate=i.DueDate FROM deleted i;
SELECT @NewDueDate=i.DueDate FROM inserted i;
IF(@OldDueDate!=@NewDueDate)
BEGIN
SET @LogDesc='Update DueDate FROM '+CONVERT(NVARCHAR(50),@OldDueDate,103)+' '+CONVERT(NVARCHAR(50),@OldDueDate,108)+' To '+CONVERT(NVARCHAR(50),@NewDueDate,103)+' '+CONVERT(NVARCHAR(50),@NewDueDate,108);-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(ContactID)
BEGIN
Declare @OldContactID bigint,@NewContactID bigint
SELECT @OldContactID=i.ContactID FROM deleted i;
SELECT @NewContactID=i.ContactID FROM inserted i;
IF(LOWER(@OldContactID) != LOWER(@NewContactID))
BEGIN
declare @OldContactName nvarchar(100), @NewContactName nvarchar(100)
SET @OldContactName = ISNULL((SELECT TOP 1 ISNULL(FirstName,'')+' '+ISNULL(LastName,'') FROM ContactMaster WHERE ContactID =@OldContactID),'')
SET @NewContactName = ISNULL((SELECT TOP 1 ISNULL(FirstName,'')+' '+ISNULL(LastName,'') FROM ContactMaster WHERE ContactID =@NewContactID),'')
SET @LogDesc='Update Contact FROM '+@OldContactName+' To '+@NewContactName;-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(Email)
BEGIN
Declare @OldEmail nvarchar(100),@NewEmail nvarchar(100)
SELECT @OldEmail=ISNULL(i.Email,'') FROM deleted i;
SELECT @NewEmail=ISNULL(i.Email,'') FROM inserted i;
IF(LOWER(@OldEmail) != LOWER(@NewEmail))
BEGIN
SET @OldEmail = CASE WHEN LEN(@OldEmail)>0 THEN @OldEmail ELSE 'None' END
SET @NewEmail = CASE WHEN LEN(@NewEmail)>0 THEN @NewEmail ELSE 'None' END
SET @LogDesc='Update Email FROM '+ISNULL(@OldEmail,'None')+' To '+ISNULL(@NewEmail,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(Mobile)
BEGIN
Declare @OldMobile nvarchar(100),@NewMobile nvarchar(100)
SELECT @OldMobile=ISNULL(i.Mobile,'') FROM deleted i;
SELECT @NewMobile=ISNULL(i.Mobile,'') FROM inserted i;
IF(LOWER(@OldMobile) != LOWER(@NewMobile))
BEGIN
SET @OldMobile = CASE WHEN LEN(@OldMobile)>0 THEN @OldMobile ELSE 'None' END
SET @NewMobile = CASE WHEN LEN(@NewMobile)>0 THEN @NewMobile ELSE 'None' END
SET @LogDesc='Update Mobile No FROM '+ISNULL(@OldMobile,'None')+' To '+ISNULL(@NewMobile,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(Phone)
BEGIN
Declare @OldPhone nvarchar(100),@NewPhone nvarchar(100)
SELECT @OldPhone=ISNULL(i.Phone,'') FROM deleted i;
SELECT @NewPhone=ISNULL(i.Phone,'') FROM inserted i;
IF(LOWER(@OldPhone) != LOWER(@NewPhone))
BEGIN
SET @OldPhone = CASE WHEN LEN(@OldPhone)>0 THEN @OldPhone ELSE 'None' END
SET @NewPhone = CASE WHEN LEN(@NewPhone)>0 THEN @NewPhone ELSE 'None' END
SET @LogDesc='Update Phone No FROM '+ISNULL(@OldPhone,'None')+' To '+ISNULL(@NewPhone,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE([Subject])
BEGIN
Declare @OldSubject nvarchar(100),@NewSubject nvarchar(100)
SELECT @OldSubject=ISNULL(i.[Subject],'') FROM deleted i;
SELECT @NewSubject=ISNULL(i.[Subject],'') FROM inserted i;
IF(LOWER(@OldSubject) != LOWER(@NewSubject))
BEGIN
SET @LogDesc='Update Subject FROM '+@OldSubject+' To '+@NewSubject;-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE([Description])
BEGIN
Declare @OldDescription nvarchar(max),@NewDescription nvarchar(max)
SELECT @OldDescription=ISNULL(i.[Description],'') FROM deleted i;
SELECT @NewDescription=ISNULL(i.[Description],'') FROM inserted i;
IF(LOWER(@OldDescription) != LOWER(@NewDescription))
BEGIN
SET @OldDescription = CASE WHEN LEN(@OldDescription)>0 THEN @OldDescription ELSE 'None' END
SET @NewDescription = CASE WHEN LEN(@NewDescription)>0 THEN @NewDescription ELSE 'None' END
SET @LogDesc='Update Description FROM '+ISNULL(@OldDescription,'None')+' To '+ISNULL(@NewDescription,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(ProductID)
BEGIN
Declare @OldProductID bigint,@NewProductID bigint
SELECT @OldProductID=ISNULL(i.ProductID,0) FROM deleted i;
SELECT @NewProductID=ISNULL(i.ProductID,0) FROM inserted i;
IF(LOWER(@OldProductID) != LOWER(@NewProductID))
BEGIN
declare @OldProductName nvarchar(100), @NewProductName nvarchar(100)
SET @OldProductName = ISNULL((SELECT TOP 1 ISNULL(ProductName,'None') From ProductMaster WHERE ProductID=@OldProductID),'None')
SET @NewProductName = ISNULL((SELECT TOP 1 ISNULL(ProductName,'None') From ProductMaster WHERE ProductID=@NewProductID),'None')
SET @LogDesc='Update Product FROM '+ISNULL(@OldProductName,'None')+' To '+ISNULL(@NewProductName,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(StatusID)
BEGIN
Declare @OldStatusID int,@NewStatusID int
SELECT @OldStatusID=ISNULL(i.StatusID,0) FROM deleted i;
SELECT @NewStatusID=ISNULL(i.StatusID,0) FROM inserted i;
IF(LOWER(@OldStatusID) != LOWER(@NewStatusID))
BEGIN
declare @OldStatusName nvarchar(100), @NewStatusName nvarchar(100)
SET @OldStatusName = ISNULL((SELECT TOP 1 ISNULL(StatusName,'None') From StatusMaster WHERE StatusID=@OldStatusID),'None')
SET @NewStatusName = ISNULL((SELECT TOP 1 ISNULL(StatusName,'None') From StatusMaster WHERE StatusID=@NewStatusID),'None')
SET @LogDesc='Update Status FROM '+ISNULL(@OldStatusName,'None')+' To '+ISNULL(@NewStatusName,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(PriorityID)
BEGIN
Declare @OldPriorityID int,@NewPriorityID int
SELECT @OldPriorityID=ISNULL(i.PriorityID,0) FROM deleted i;
SELECT @NewPriorityID=ISNULL(i.PriorityID,0) FROM inserted i;
IF(LOWER(@OldPriorityID) != LOWER(@NewPriorityID))
BEGIN
declare @OldPriorityName nvarchar(100), @NewPriorityName nvarchar(100)
SET @OldPriorityName = ISNULL((SELECT TOP 1 ISNULL(PriorityName,'None') From PriorityMaster WHERE PriorityID=@OldPriorityID),'None')
SET @NewPriorityName = ISNULL((SELECT TOP 1 ISNULL(PriorityName,'None') From PriorityMaster WHERE PriorityID=@NewPriorityID),'None')
SET @LogDesc='Update Priority FROM '+ISNULL(@OldPriorityName,'None')+' To '+ISNULL(@NewPriorityName,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(ChannelID)
BEGIN
Declare @OldChannelID int,@NewChannelID int
SELECT @OldChannelID=ISNULL(i.ChannelID,0) FROM deleted i;
SELECT @NewChannelID=ISNULL(i.ChannelID,0) FROM inserted i;
IF(LOWER(@OldChannelID) != LOWER(@NewChannelID))
BEGIN
declare @OldChannelName nvarchar(100), @NewChannelName nvarchar(100)
SET @OldChannelName = ISNULL((SELECT TOP 1 ISNULL(ChannelName,'None') From ChannelMaster WHERE ChannelID=@OldChannelID),'None')
SET @NewChannelName = ISNULL((SELECT TOP 1 ISNULL(ChannelName,'None') From ChannelMaster WHERE ChannelID=@NewChannelID),'None')
SET @LogDesc='Update Channel FROM '+ISNULL(@OldChannelName,'None')+' To '+ISNULL(@NewChannelName,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(ClassificationID)
BEGIN
Declare @OldClassificationID int,@NewClassificationID int
SELECT @OldClassificationID=ISNULL(i.ClassificationID,0) FROM deleted i;
SELECT @NewClassificationID=ISNULL(i.ClassificationID,0) FROM inserted i;
IF(LOWER(@OldClassificationID) != LOWER(@NewClassificationID))
BEGIN
declare @OldClassificationName nvarchar(100), @NewClassificationName nvarchar(100)
SET @OldClassificationName = ISNULL((SELECT TOP 1 ISNULL(ClassificationName,'None') From ClassificationMaster WHERE ClassificationID=@OldClassificationID),'None')
SET @NewClassificationName = ISNULL((SELECT TOP 1 ISNULL(ClassificationName,'None') From ClassificationMaster WHERE ClassificationID=@NewClassificationID),'None')
SET @LogDesc='Update Classification FROM '+ISNULL(@OldClassificationName,'None')+' To '+ISNULL(@NewClassificationName,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
--INSERT INTO TicketLogMaster
-- (TicketID,[Description],CreatedBy,CompanyCode)Values
-- (@TicketID,@LogDesc,@NewModifiedBy,@CompanyCode)
SET @LogDesc = '';
END
END
IF UPDATE(TicketOwner)
BEGIN
Declare @OldTicketOwner nvarchar(50),@NewTicketOwner nvarchar(50)
SELECT @OldTicketOwner=ISNULL(i.[TicketOwner],0) FROM deleted i;
SELECT @NewTicketOwner=ISNULL(i.[TicketOwner],0) FROM inserted i;
IF(LOWER(@OldTicketOwner) != LOWER(@NewTicketOwner))
BEGIN
Declare @OldTicketOwnerName nvarchar(512) , @NewTicketOwnerName nvarchar(512)
SET @OldTicketOwnerName = ISNULL((SELECT TOP 1 ISNULL(UserName,'None') From UserMaster WHERE UserCode=@OldTicketOwner),'None')
SET @NewTicketOwnerName = ISNULL((SELECT TOP 1 ISNULL(UserName,'None') From UserMaster WHERE UserCode=@NewTicketOwner),'None')
SET @LogDesc='Update Ticket Owner FROM '+ISNULL(@OldTicketOwnerName,'None')+' To '+ISNULL(@NewTicketOwnerName,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE([Resolution])
BEGIN
Declare @OldResolution nvarchar(max),@NewResolution nvarchar(max)
SELECT @OldResolution=ISNULL(i.[Resolution],'') FROM deleted i;
SELECT @NewResolution=ISNULL(i.[Resolution],'') FROM inserted i;
IF(LOWER(@OldResolution) != LOWER(@NewResolution))
BEGIN
SET @OldResolution = CASE WHEN LEN(@OldResolution)>0 THEN @OldResolution ELSE 'None' END
SET @NewResolution = CASE WHEN LEN(@NewResolution)>0 THEN @NewResolution ELSE 'None' END
SET @LogDesc='Update Resolution FROM '+ISNULL(@OldResolution,'None')+' To '+ISNULL(@NewResolution,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END
IF UPDATE(FeedBackRating)
BEGIN
DECLARE @NewFeedBack NVARCHAR(50)
SELECT @NewFeedBack=ISNULL(i.FeedBackRating,'') FROM inserted i;
SET @LogDesc='FeedBack : FROM None To '+ISNULL(@NewFeedBack,'None');-- INSTEAD OF UPDATE After Update Trigger.
EXECUTE [dbo].[TicketLogMasterInsert] @TicketID,@LogDesc,@NewModifiedBy,@CompanyCode
SET @LogDesc = '';
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
BEGIN TRANSACTION siteerror
Declare @ErrorCode VARCHAR(50), @ExceptionMessage VARCHAR(MAX), @Source VARCHAR(512),@ErrorDesc VARCHAR(MAX)
SET @ErrorCode=(SELECT CAST(ISNULL(ERROR_NUMBER(),'') AS VARCHAR))
SET @Source=(SELECT ISNULL('TriggerAfterUpdateTicketMaster',''))
SET @ExceptionMessage=(SELECT ISNULL(ERROR_MESSAGE(),''))
SET @ErrorDesc=(SELECT 'Severity : ' + CAST(ISNULL(ERROR_SEVERITY(),'') AS VARCHAR(50)) + ' State : ' + CAST(ISNULL(ERROR_STATE(),'') AS VARCHAR(50)) + ' Line : ' + CAST(ISNULL(ERROR_LINE(),'') AS VARCHAR(50)))
EXECUTE [dbo].[SiteErrorInsert]
@ErrorCode,@ExceptionMessage,'',@Source,'','',@ErrorDesc,'',@NewModifiedBy,@CompanyCode
COMMIT TRANSACTION siteerror
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
No comments:
Post a Comment