locked
SQL Trigger for SQL Merge Insert RRS feed

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

    Friday, May 20, 2016 11:42 AM

Answers

All replies

  • 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
    

    Friday, May 20, 2016 1:46 PM
  • 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.

    Saturday, May 21, 2016 2:03 AM
  • 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

     

    Sunday, May 22, 2016 8:40 AM
  • User-1345054863 posted

    Thanks for the replay Zhang,

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

    Tuesday, May 24, 2016 2:17 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 30, 2016 11:15 AM