troubleshooting Question

Why SQL server trigger not insert records during insert operation?

Avatar of Varshini S
Varshini S asked on
Microsoft SQL ServerSQL
7 Comments2 Solutions19 ViewsLast Modified:
I have the below insert, update trigger. Update trigger inserts the records in the "TicketMaster_History" table whenever the update operation happens in "Ticketmaster" table.
But the insert triggers not inserting a record in "TicketMaster_History_New" whenever the new record inserted in in "Ticketmaster" table.
I am using the SQL merge command to update or insert records in the Ticketmaster table.

Below is the trigger created in Ticketmaster table:

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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 7 Comments.
Try for 7 days

”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.

-Mike Kapnisakis, Warner Bros