Answered by:
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;
ENDHere 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
-
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
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