SQL Trigger for SQL Merge Insert

Question

User-1345054863 posted

Hello friends,

I have a situation to which i need help.

I have 2 SQL table (having PK and FK relationship). i will do INSERT / UPDATE / DELETE on table T1. On inserting i have written a Trigger for inserting records into table T2 based on the values on table T1.

Here is the stored procedure

-- =========================================================    
ALTER PROCEDURE [dbo].[uspc_wizard_ESS_PA2001_inft2001_absence]      
     
 @Tbl [dbo].[UDTT_ESS_PA2001_inft2001_absence] READONLY,
 @fld_searchtxt VARCHAR(MAX),  
 @fld_flag INT    
     
      
AS    
BEGIN    
SET NOCOUNT ON;                       
DECLARE @intErrorCode INT                     
BEGIN TRAN      
    
    
------------------------- GET ALL tbl_ESS_PA2001_inft2001_absence DETAILS ----------- START ---------------    
IF(@fld_flag = 0)    
BEGIN     
 SELECT * FROM [dbo].[tbl_ESS_PA2001_inft2001_absence]  
END     
------------------------- GET ALL tbl_ESS_PA2001_inft2001_absence DETAILS ----------- END -----------------    
--------------------------- INSERT tbl_ESS_PA2001_inft2001_absence DETAILS -------- START ------    
IF(@fld_flag = 1)    
BEGIN

    DECLARE @Tmp TABLE
    (
        [$action] [varchar](20)  NULL,
        [PERNR] [char](8)  NULL
    )
     
      MERGE INTO [dbo].[tbl_ESS_PA2001_inft2001_absence] T1
      USING @Tbl T2
      ON T1.PERNR    = T2.PERNR
      AND T1.AWART    = T2.AWART
      AND T1.BEGDA    = T2.BEGDA
      AND T1.ENDDA    = T2.ENDDA

      WHEN MATCHED
      THEN
      UPDATE SET T1.BEGUZ = LTRIM(RTRIM(T2.BEGUZ))
      , T1.ENDUZ = LTRIM(RTRIM(T2.ENDUZ))
      , T1.STDAZ = LTRIM(RTRIM(T2.STDAZ))
      
      WHEN NOT MATCHED
      THEN 
      INSERT VALUES
           (LTRIM(RTRIM(T2.PERNR)), LTRIM(RTRIM(T2.AWART)), LTRIM(RTRIM(T2.BEGDA)), LTRIM(RTRIM(T2.ENDDA))
           , LTRIM(RTRIM(T2.BEGUZ)), LTRIM(RTRIM(T2.ENDUZ)), LTRIM(RTRIM(T2.STDAZ)), '-', 'SENT', GETDATE()
           , '-', GETDATE(), '-', GETDATE(), '-')
      WHEN NOT MATCHED BY SOURCE THEN
      DELETE
     
     OUTPUT $action [$action],coalesce (inserted.PERNR, deleted.PERNR) INTO @Tmp;     

     SELECT DISTINCT * FROM @Tmp;
      
END       

Here is trigger written for another table called tbl_ESS_PTREQ_HEADER

CREATE TRIGGER [dbo].[trg_PA2001_absence]
   ON  [dbo].[tbl_ESS_PA2001_inft2001_absence]
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;
	DECLARE @Req_Id INT = (SELECT INSERTED.REQ_ID FROM INSERTED)
	DECLARE @BEGDA DATE = (SELECT INSERTED.BEGDA FROM INSERTED)
	DECLARE @ENDDA DATE = (SELECT INSERTED.ENDDA FROM INSERTED)
	WHILE(@BEGDA <= @ENDDA) 
	BEGIN
		INSERT INTO [dbo].[tbl_ESS_PTREQ_HEADER]
           ([REQ_ID]
           ,[REQ_DATE]
           ,[TABLE_TYPE])
		VALUES
           ( @Req_Id
           , @BEGDA
           , 'PA2001')
		SET @BEGDA = (SELECT DATEADD(DAY,1,@BEGDA))   
	END
END
GO

But its throwing error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help is appreciated. Thanks in advance.

Answer 1

User77042963 posted

CREATE TRIGGER [dbo].[trg_PA2001_absence]
   ON  [dbo].[tbl_ESS_PA2001_inft2001_absence]
   AFTER INSERT
AS 
BEGIN
 SET NOCOUNT ON;

INSERT INTO [dbo].[tbl_ESS_PTREQ_HEADER]
           ([REQ_ID]
           ,[REQ_DATE]
           ,[TABLE_TYPE]) 

Select INSERTED.REQ_ID, INSERTED.BEGDA,  'PA2001' FROM INSERTED 

END
GO

Answer 2

User-1345054863 posted

Thanks for the replay, But my requirement is, i want insert the Date range between BEGDA and ENDDA to tbl_ESS_PTREQ_HEADER table. For example i have a record in tbl_ESS_PA2001_inft2001_absence table like

REQ_ID	PERNR	        AWART	BEGDA	        ENDDA	
1	00000061	1000	2016-05-5	2016-05-10	
2	00000062	1001	2016-04-13	2016-04-15
..........

I want to insert data to tbl_ESS_PTREQ_HEADER table like

REQ_ID	REQ_DATE	TABLE_TYPE
1	2016-05-05	PA2001
1	2016-05-06	PA2001
1	2016-05-07	PA2001
1	2016-05-08	PA2001
1	2016-05-09	PA2001
1	2016-05-10	PA2001
2	2016-04-13	PA2001
2	2016-04-14	PA2001
2	2016-04-15	PA2001
........ So on ---

I have to loop through Dates BEGDA an ENDDA and insert the Date range to tbl_ESS_PTREQ_HEADER table.

Please suggest me a any method to achieve this.

Answer 3

User-219423983 posted

Hi Raghavendra32,

I have created a demo as below to achieve your need and you could have a look and make some changes according to your need.

In the demo, I use two while loop to insert the expected data. The outer while loop gets the "@BEGDA" and "@BEGDA" from "inserted" table, the inner one creates the records to  "tbl_ESS_PTREQ_HEADER".

create table tbl_ESS_PA2001_inft2001_absence(REQ_ID int, BEGDA datetime,ENDDA datetime)

go

create table tbl_ESS_PTREQ_HEADER (REQ_ID int,REQ_DATE datetime, TABLE_TYPE varchar(20))

go

CREATE TRIGGER [dbo].[trg_PA2001_absence]
   ON  [dbo].[tbl_ESS_PA2001_inft2001_absence]
   AFTER INSERT
AS 
BEGIN
	
	SET NOCOUNT ON;
	declare @currentCount int = 1
	Declare @RowCount int = (select COUNT(*) from INSERTED)


	select (ROW_NUMBER() over (order by REQ_ID)) as RowNum,* into #temp from inserted


	WHILE(@currentCount <= @RowCount) 
	BEGIN
	
		DECLARE @Req_Id INT = (SELECT REQ_ID FROM #temp where RowNum = @currentCount)
		DECLARE @BEGDA DATE = (SELECT BEGDA FROM #temp where RowNum = @currentCount)
		DECLARE @ENDDA DATE = (SELECT ENDDA FROM #temp where RowNum = @currentCount)
		WHILE(@BEGDA <= @ENDDA) 
		BEGIN
		
			INSERT INTO [dbo].[tbl_ESS_PTREQ_HEADER]
			   ([REQ_ID]
			   ,[REQ_DATE]
			   ,[TABLE_TYPE])
			VALUES
			   ( @Req_Id
			   , @BEGDA
			   , 'PA2001')
			SET @BEGDA = (SELECT DATEADD(DAY,1,@BEGDA))   
		END

		SET @currentCount = @currentCount+1   
	END
END
GO

insert into tbl_ESS_PA2001_inft2001_absence values (1,'2016-05-5',	'2016-05-10'),(2,'2016-04-13',	'2016-04-15')


select * from tbl_ESS_PA2001_inft2001_absence
select * from tbl_ESS_PTREQ_HEADER


drop table tbl_ESS_PA2001_inft2001_absence,tbl_ESS_PTREQ_HEADER

---The final result---
REQ_ID	REQ_DATE	TABLE_TYPE
1	2016-05-05 00:00:00.000	PA2001
1	2016-05-06 00:00:00.000	PA2001
1	2016-05-07 00:00:00.000	PA2001
1	2016-05-08 00:00:00.000	PA2001
1	2016-05-09 00:00:00.000	PA2001
1	2016-05-10 00:00:00.000	PA2001
2	2016-04-13 00:00:00.000	PA2001
2	2016-04-14 00:00:00.000	PA2001
2	2016-04-15 00:00:00.000	PA2001

Best Regards,

Albert Zhang

 

Answer 4

User-1345054863 posted

Thanks for the replay Zhang,

Can we simplify the solution by using CTE of table variable rather than using Temp table....?

Answer 5

User-219423983 posted

Hi Raghavendra32,

Yes, you can. You could modify the above code according to your need. Besides, maybe it’s not related to your need directly, but you could also have a look at the differences between “CTE Table” and temporary table.

http://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables

Best Regards,

Weibo Zhang