Need Trigger ,On Insert Record?

Question

I need trigger on insert record ,that when i insert record into table #tbl_GRN_M, then at the base of col (GRN_Qty) qty ,that rows get insert into table #tbl_GRN_D.

Create table #tbl_GRN_M  (GRN_ID int,Level_Five_ID int,GRN_Qty int)
Create table #tbl_GRN_D  (GRN_D_ID int,GRN_ID int)


Insert into #tbl_GRN_M (GRN_ID,Level_Five_ID,GRN_Qty) values (101,111,6)
Insert into #tbl_GRN_D (GRN_D_ID,GRN_ID) values (1001,101)
Insert into #tbl_GRN_D (GRN_D_ID,GRN_ID) values (1002,101)
Insert into #tbl_GRN_D (GRN_D_ID,GRN_ID) values (1003,101)
Insert into #tbl_GRN_D (GRN_D_ID,GRN_ID) values (1004,101)
Insert into #tbl_GRN_D (GRN_D_ID,GRN_ID) values (1005,101)
Insert into #tbl_GRN_D (GRN_D_ID,GRN_ID) values (1006,101)

Answer 1

Please check:

Create table tbl_GRN_M  (GRN_ID int,Level_Five_ID int,GRN_Qty int)  
Create table tbl_GRN_D  (GRN_D_ID int,GRN_ID int,GRN_Qty int)  
  
CREATE TRIGGER inserttbl_GRN_M  
ON tbl_GRN_M  
FOR INSERT   
AS  
BEGIN  
 DECLARE @num int  
 SET @num=1  
 WHILE @num<=(SELECT GRN_Qty FROM inserted i WHERE GRN_ID=i.GRN_ID)    
 BEGIN   
   INSERT INTO tbl_GRN_D(GRN_ID,GRN_Qty)  
   SELECT GRN_ID,@num  
   FROM inserted i     
   WHERE GRN_ID=i.GRN_ID  
   SET @num=@num+1  
  END  
END  
  
Insert into tbl_GRN_M (GRN_ID,Level_Five_ID,GRN_Qty) values (101,111,6)    
Insert into tbl_GRN_M (GRN_ID,Level_Five_ID,GRN_Qty) values (102,111,3)    
  
SELECT * FROM  tbl_GRN_M   
SELECT * FROM tbl_GRN_D  
  
DROP TRIGGER inserttbl_GRN_M  
DROP TABLE tbl_GRN_D  
DROP TABLE tbl_GRN_M   

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Try this:

CREATE TABLE dbo.tbl_GRN_M (
    GRN_ID int,
    Level_Five_ID int,
    GRN_Qty int
);

CREATE TABLE dbo.tbl_GRN_D (
    GRN_D_ID int IDENTITY(1, 1),
    GRN_ID int
);
GO

CREATE TRIGGER TI_tbl_GRN_M ON dbo.tbl_GRN_M 
FOR INSERT 
AS
BEGIN
    DECLARE @i int;
    DECLARE @GRN_ID int;

    SELECT @GRN_ID = GRN_ID, @i = GRN_Qty
    FROM inserted;

    ;WITH CTE AS (
        SELECT 1 AS i
        UNION ALL
        SELECT i + 1 AS i
        FROM CTE 
        WHERE i < @i
    )

    INSERT INTO dbo.tbl_GRN_D (GRN_ID)
    SELECT @GRN_ID
    FROM inserted AS i, CTE AS c;
END
GO

INSERT INTO dbo.tbl_GRN_M VALUES (101, 111, 6);
GO

SELECT * FROM dbo.tbl_GRN_M;
SELECT * FROM dbo.tbl_GRN_D;
GO

Answer 3

Hi @Analyst_SQL ,

 Create table tbl_GRN_M  (GRN_ID int,Level_Five_ID int,GRN_Qty int)  
 Create table tbl_GRN_D  (GRN_D_ID int,GRN_ID int)  
  
 CREATE TRIGGER insertEmployees  
 ON tbl_GRN_M  
 FOR INSERT   
 AS  
 BEGIN  
 INSERT INTO tbl_GRN_D(GRN_ID)  
 SELECT GRN_ID  
 FROM inserted i  
 WHERE GRN_ID=i.GRN_ID  
 END  
  
 DROP TRIGGER insertEmployees  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

If so, you need to work with a table of numbers of some sort. I have an short article on my web site there you can learn about this concept:
https://www.sommarskog.se/Short%20Stories/table-of-numbers.html