ALTER TRIGGER [dbo].[trg_TicketMaster_Update]
ON [dbo].[TicketMaster]
AFTER UPDATE,INSERT,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Client_code [char](20)
Declare @TicketID bigint
Declare @Subject [nvarchar](500)
Declare @Work_Status [varchar](max)
Declare @New_Work_Status [varchar](max)
Declare @Nstat [varchar](max)
Declare @Ticket_Status [varchar](max)
Declare @New_Ticket_Status [varchar](max)
Declare @Priority [varchar](50)
Declare @New_Priority [varchar](50)
Declare @Agent [varchar](100)
Declare @New_Agent [varchar](100)
Declare @Created_time [datetime]
Declare @Tags [varchar](100)
Declare @ETA [datetime]
Declare @New_ETA [datetime]
Declare @Today [datetime]
Declare @A [bigint]
Declare @D [bigint]
Declare @Duration [bigint]
Declare @CSC_Agent [varchar](50)
Declare @New_CSC_Agent [varchar](50)
Declare @Closed_On [date]
Declare @Remarks [varchar](max)
select @Client_code=Client_code,@TicketID=TicketID,
@Subject=@Subject ,
@Work_Status=Work_Status ,
@Nstat=Nstat,
@Ticket_Status=Ticket_Status ,
@Priority=Priority,
@Agent=Agent,
@Created_time=Created_time,
@Tags=Tags,
@ETA=ETA,
@Today=Today,
@A=A ,
@D=D ,
@Duration=Duration ,
@CSC_Agent=CSC_Agent,
@Closed_On=Closed_On,
@Remarks=Remarks FROM deleted
---UPDATE RECORDS
if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
select @New_Work_Status=Work_Status,@New_Ticket_Status=Ticket_Status,
@New_Priority=Priority,
@New_Agent=Agent,
@New_ETA=ETA,
@New_CSC_Agent=CSC_Agent FROM INSERTED;
Insert into TicketMaster_History(Client_code,[TicketID],[Subject],[Work_Status],
[New_Work_Status] ,[Nstat] ,[Ticket_Status] ,
[New_Ticket_Status] ,[Priority] ,[New_Priority],
[Agent] ,[New_Agent] ,[Created_time] ,[Tags] ,
[ETA] ,[New_ETA] ,[Today] ,[A] ,[D],[Duration] ,
[CSC_Agent] ,[New_CSC_Agent] ,[Closed_On] ,[Remarks] ) Values (
@Client_code,
@TicketID , @Subject,@Work_Status,@New_Work_Status,
@Nstat,@Ticket_Status,@New_Ticket_Status,@Priority,
@New_Priority,@Agent,@New_Agent,@Created_time,@Tags,
@ETA,@New_ETA,@Today,@A,@D,@Duration,@CSC_Agent,@New_CSC_Agent,@Closed_On,
@Remarks)
end
--- NEW RECORDS
If exists (Select * from inserted) and not exists(Select * from deleted)
begin
select @Client_code=Client_code,@TicketID=TicketID,
@Subject=@Subject ,
@Work_Status=Work_Status ,
@Nstat=Nstat,
@Ticket_Status=Ticket_Status ,
@Priority=Priority,
@Agent=Agent,
@Created_time=Created_time,
@Tags=Tags,
@ETA=ETA,
@Today=Today,
@A=A ,
@D=D ,
@Duration=Duration ,
@CSC_Agent=CSC_Agent,
@Closed_On=Closed_On,
@Remarks=Remarks FROM inserted ;
Insert into TicketMaster_History_New(Client_code,[TicketID],[Subject],[Work_Status],
[New_Work_Status] ,[Nstat] ,[Ticket_Status] ,
[New_Ticket_Status] ,[Priority] ,[New_Priority],
[Agent] ,[New_Agent] ,[Created_time] ,[Tags] ,
[ETA] ,[New_ETA] ,[Today] ,[A] ,[D],[Duration] ,
[CSC_Agent] ,[New_CSC_Agent] ,[Closed_On] ,[Remarks] ) Values (
@Client_code,
@TicketID , @Subject,@Work_Status,@New_Work_Status,
@Nstat,@Ticket_Status,@New_Ticket_Status,@Priority,
@New_Priority,@Agent,@New_Agent,@Created_time,@Tags,
@ETA,@New_ETA,@Today,@A,@D,@Duration,@CSC_Agent,@New_CSC_Agent,@Closed_On,
@Remarks)
end
END
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.