Update triger ,when order number is changed.

Analyst_SQL 3,551 Reputation points
2021-09-23T08:51:22.17+00:00

Below is my data ,i want when i update #tbl_BalPacM orderno from 15 to 21 ,then trigger run to update #tbl_BalPacM orderno then #tbl_PckDetail orderno on same PID number,then Update orderno in #probale table against Prdno ,which exit in #tbl_PckDetail

    Create Table #itemmasterfile(CodeItem int,CID  int,Descriptionitem varchar(50),Packsize varchar(50)) 
   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)
   Create table #tbl_PckDetail (DID int,PID int,Orderno int, prdno int)




   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)  
   INSERT INTO #Probale VALUES(10002,15,3,270,1,'2020-10-21',null,111) 
   INSERT INTO #Probale VALUES(10003,15,4,270,1,'2020-10-21',null,111)  
   INSERT INTO #Probale VALUES(10004,16,5,270,1,'2020-10-21',null,112)  
   INSERT INTO #Probale VALUES(10005,16,6,270,1,'2020-10-21',null,112)




Insert into #tbl_BalPacM values (111,15)
insert into #tbl_PckDetail values(101,111,15,10000)
insert into #tbl_PckDetail values(101,111,15,10002)
insert into #tbl_PckDetail values(101,111,15,10003)
insert into #tbl_PckDetail values(101,111,15,10004)
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,330 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-09-24T00:48:54.87+00:00

    Hi @Analyst_SQL ,

    What is the relationship between #Probale and #tbl_PckDetail?

    Only the column prdno or both prdno and PID should be matched?

    If only the column prdno, please refer below updated one.

         create trigger mytriggerorderno  on tbl_BalPacM  
         after update   
         as  
         begin  
             if update (orderno)  
             begin  
             update a  
             set a.orderno=b.orderno  
             from tbl_PckDetail a  
             inner join inserted b  
             on a.pid=b.pid  
              
             update a  
             set a.orderno=b.orderno  
             from Probale a  
             inner join tbl_PckDetail b  
             on a.prdno=b.prdno  
              inner join inserted c  
              on c.pid=b.pid  
             end  
         end  
    

    If both prdno and PID should be matched, please refer below:

    create trigger mytriggerorderno  on tbl_BalPacM  
         after update   
         as  
         begin  
             if update (orderno)  
             begin  
             update a  
             set a.orderno=b.orderno  
             from tbl_PckDetail a  
             inner join inserted b  
             on a.pid=b.pid  
              
             update a  
             set a.orderno=b.orderno  
             from Probale a  
             inner join tbl_PckDetail b  
             on a.prdno=b.prdno and a.pid and b.pid  
              inner join inserted c  
              on c.pid=b.pid  
             end  
         end  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


4 additional answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-09-23T09:02:54.497+00:00

    Hi @Analyst_SQL ,

    Trigger could not be created on Temporary tables.

    Please refer below and check whether it is working:

    create trigger mytriggerorderno  on tbl_BalPacM  
    after update   
    as  
    begin  
    	if update (orderno)  
    	begin  
    	update a  
    	set a.orderno=b.orderno  
    	from tbl_PckDetail a  
    	inner join inserted b  
    	on a.pid=b.pid  
      
    	update a  
    	set a.orderno=b.orderno  
    	from Probale a  
    	inner join tbl_PckDetail b  
    	on a.prdno=b.prdno  
    	end  
    end  
    

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Olaf Helper 43,166 Reputation points
    2021-09-23T09:21:05.843+00:00

    tbl_BalPacM orderno

    Is ordnerno in the table unique = primary key?
    Then create a foreign key with CASCADE UPDATE, then you don't need a trigger => Create Foreign Key Relationships

    If it's not the PK, then your requirements don't make much sense and will fail.


  3. Tom Phillips 17,721 Reputation points
    2021-09-23T16:36:13.917+00:00

    The problem is with your data model.

    There is no reason to have orderno in tbl_PckDetail, when it can be looked up in tbl_BalPacM.


  4. MelissaMa-MSFT 24,186 Reputation points
    2021-09-28T01:06:59.28+00:00

    Hi @Analyst_SQL ,

    Thanks for your update.

    Which trigger gives the error?

    If the first trigger (only the column prdno match) is working, you could refer that one.

        create trigger mytriggerorderno  on tbl_BalPacM  
          after update   
          as  
          begin  
              if update (orderno)  
              begin  
              update a  
              set a.orderno=b.orderno  
              from tbl_PckDetail a  
              inner join inserted b  
              on a.pid=b.pid  
                  
              update a  
              set a.orderno=b.orderno  
              from Probale a  
              inner join tbl_PckDetail b  
              on a.prdno=b.prdno  
               inner join inserted c  
               on c.pid=b.pid  
              end  
          end  
    

    If above is not working, please provide the related data so that we could fix this issue.

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments