Avatar of Varshini S
Varshini S

asked on 

Why SQL server trigger not insert records during insert operation?

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

Open in new window

Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Varshini S
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

you got one mistake in your code:

 @Subject=@Subject  ,

should be as

 @Subject=Subject  ,

But the insert triggers not inserting a record in "TicketMaster_History_New" whenever the new record inserted in in "Ticketmaster" table.

that seems not to be the case.

for the insertion part, it inserted into TicketMaster_History_New
Avatar of Varshini S
Varshini S

ASKER

Thanks, Ryan,

I have corrected the @Subject=Subject  

The logic is - whenever I update the existing  record in "Ticketmaster" table - need to insert the record in "TicketMaster_History"  table
whenever I insert the new record in the "Ticketmaster" table - need to insert the record in "TicketMaster_History_New"  table
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

i have tested with similar structure and it worked for me.
Avatar of Varshini S
Varshini S

ASKER

The new records are not inserted in TicketMaster_History_New table. Updates works fine.

Does SQL Server triggers support Merage?
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of Varshini S
Varshini S

ASKER

Thank you Ryan Chong and Anders Ebro
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo