Need trigger for inserting data into table,after inserting data

Question

Need trigger for inserting data into table,after inserting data

Analyst_SQL 3,576

Below is table

      CREATE TABLE #Probale (prdno INT,orderno int,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int,PID int)   
      Create table #tbl_BalPacM (PID int ,Orderno int,del int)  
      Create table #tbl_PckDetail (DID int,PID int, prdno int,Qty int ,delid int)

I want, when i insert data into #Probale table ,when trigger get fire and insert data into #Tbl_BalPacm and #tbl_PckDetail table.

      INSERT INTO #Probale VALUES(10000,15,1,270,1,'2020-10-21',null,111)   
      INSERT INTO #Probale VALUES(10001,15,2,270,1,'2020-10-21',null,111)

I am generating PID using Store procedure

Create Proc [dbo].[SP_Generate_Pack_ID]
as begin

;with cte as (select 10001 as PID  
  
UNION ALL  
  
select top (1) PID + 1 as PID  
  
from tbl_BalPacM t order by t.PID  DESC)  
  
select top 1 PID  
  
from cte ORDER BY PID DESC

output.

Tom Phillips 17,781 Reputation points

2022-03-28T12:40:27.237+00:00

It is not clear exactly what you want the trigger to do.

You do not need a trigger to get the PID in an autogenerated identity field. You can use OUTPUT on insert.
Analyst_SQL 3,576 Reputation points

2022-03-28T12:58:46.94+00:00
i want to trigger for inserting data into below table,Prdno will get from #Probale table

Create table #tbl_BalPacM (PID int ,Orderno int,del int) Create table #tbl_PckDetail (DID int,PID int, prdno int,Qty int ,delid int)
Tom Phillips 17,781 Reputation points

2022-03-28T14:46:57.403+00:00

Do you need a trigger? Triggers are very rarely needed in SQL Server.

Your example is all temp tables. Are you actually trying to insert into actual tables from a temp table and just need to generate the appropriate process to insert into the target tables?
Analyst_SQL 3,576 Reputation points

2022-03-28T18:32:57.073+00:00

yes ,first insert into Probale table then trigger fire and data get insert into tbl_BalPacM and tbl_PckDetail table
Naomi Nosonovsky 8,881 Reputation points

2022-03-28T19:04:24.187+00:00

If you insist on the trigger, you can use INSERTED table and write the trigger's code similar to what I showed you for AFTER UPDATE trigger. In this case it will be AFTER INSERT and the Inserted table will be available so you can use it as a real table.

Analyst_SQL 3,576

then what about PID ,which is being generated by Store procedure

below

Create Proc [dbo].[SP_Generate_Pack_ID]
as begin

 ;with cte as (select 10001 as PID

 UNION ALL

 select top (1) PID + 1 as PID

 from tbl_BalPacM t order by t.PID  DESC)

 select top 1 PID

 from cte ORDER BY PID DESC

Naomi Nosonovsky 8,881 Reputation points

2022-03-29T03:59:45.163+00:00

What this procedure has to do with the trigger? BTW, I suggest to not name it using sp_ prefix.

2 answers

Your answer

Tom Phillips 17,781 Reputation points

2022-03-28T12:40:27.237+00:00

It is not clear exactly what you want the trigger to do.

You do not need a trigger to get the PID in an autogenerated identity field. You can use OUTPUT on insert.
Analyst_SQL 3,576 Reputation points

2022-03-28T12:58:46.94+00:00

i want to trigger for inserting data into below table,Prdno will get from #Probale table

Create table #tbl_BalPacM (PID int ,Orderno int,del int) Create table #tbl_PckDetail (DID int,PID int, prdno int,Qty int ,delid int)
Tom Phillips 17,781 Reputation points

2022-03-28T14:46:57.403+00:00

Do you need a trigger? Triggers are very rarely needed in SQL Server.

Your example is all temp tables. Are you actually trying to insert into actual tables from a temp table and just need to generate the appropriate process to insert into the target tables?
Analyst_SQL 3,576 Reputation points

2022-03-28T18:32:57.073+00:00

yes ,first insert into Probale table then trigger fire and data get insert into tbl_BalPacM and tbl_PckDetail table
Naomi Nosonovsky 8,881 Reputation points

2022-03-28T19:04:24.187+00:00

If you insist on the trigger, you can use INSERTED table and write the trigger's code similar to what I showed you for AFTER UPDATE trigger. In this case it will be AFTER INSERT and the Inserted table will be available so you can use it as a real table.
Analyst_SQL 3,576 Reputation points

2022-03-29T03:14:27.047+00:00

then what about PID ,which is being generated by Store procedure

below

Create Proc [dbo].[SP_Generate_Pack_ID] as begin ;with cte as (select 10001 as PID UNION ALL select top (1) PID + 1 as PID from tbl_BalPacM t order by t.PID DESC) select top 1 PID from cte ORDER BY PID DESC
Naomi Nosonovsky 8,881 Reputation points

2022-03-29T03:59:45.163+00:00

What this procedure has to do with the trigger? BTW, I suggest to not name it using sp_ prefix.

Answer 1

Naomi Nosonovsky 8,881

What is the parent and what is the child in your tables? It looks to me (I may be wrong) that tbl_BalPackM is the parent to other tables. If this is the case, then we need to insert information into this table first and then the rest of the tables.

Analyst_SQL 3,576 Reputation points

2022-04-05T01:19:10.057+00:00

@Anonymous
I am waiting for you response?

Answer 2

Analyst_SQL 3,576

First table is below,

    CREATE TABLE #Probale (prdno INT,orderno int,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int,PID int)

when data get inserted into #Probale table, then trigger will fire and data get insert into

table (#tbl_BalPacM ) it is Parent table of #tbl_PckDetail,

Means PID will insert in tbl_BalPacM and tbl_PckDetail as well,then Prdno from #probale table will get insert simultaneously,

Note: I am using Store Procedure for generating PID

Tom Phillips 17,781 Reputation points

2022-03-29T12:27:14.09+00:00

Is there a reason why you are not using an IDENTITY field for PID?
Analyst_SQL 3,576 Reputation points

2022-03-29T15:33:18.197+00:00

I want solution,is there any way to use trigger to achieve this task

Share via

Need trigger for inserting data into table,after inserting data

2 answers

Your answer