1. Aditya Kabir
  2. PowerBuilder
  3. Friday, 14 June 2024 03:05 AM UTC

I'm working on a legacy project which connect to an older version sql server , my SQL Server version is 2008 R2, I encountered a problem which when I tried to insert the records within a datawindow in a PowerBuilder program, it triggered a trigger in a table  , at the same time it update another table which trigger another trigger as well,  it went wrong in the second table trigger. the tricky part was debugging a trigger ,any idea how to deal with this problem?

my first trigger

ALTER Trigger  [table1_Insert] 
On  [table1] For Insert
As
.........
.........
declare @ln_lestnum numeric(12,4)
declare @ln_num  numeric(9,3)
if exists (select att_no from inserted where source='in' and att_no <> '' and LOWER(LEFT( att_no, 3)) ='bac' )
begin
 -- here is trigger anotehr table update trigger
   update s 
      set s.process_num = s.process_num + i.process_num,
      s.is_process = case when s.back_num  - i.process_num > 0.000001 then 'yes' else 'ok' end
      from inserted i,secondtable s  
      where ltrim(rtrim(i.att_no)) =  ltrim(rtrim(s.back_no)) and  
      i.prod_no = s.prod_no and 
      i.prod_add = s.prod_add and 
      i.batch_no = s.batch_no 
      if @@rowcount = 0 
       begin
          print('errors encountered update returning goods numbers')
          eturn
       end
  end 

 here is my second trigger for update

 

ALTER trigger [secondtable_update]
on  [secondtable]
for update
as

declare @ln_lestnum numeric(9,3)
declare @ln_newbacknum  numeric(9,3)
declare @ln_oldbacknum  numeric(9,3)
declare @ls_prodno char(8),@ls_batchno char(12),@ls_prodadd char(12)

select  @ls_prodno = i.prod_no,@ls_batchno=i.batch_no,@ls_prodadd=prod_add,
        @ln_newbacknum  = i.back_num from inserted i
select  @ln_oldbacknum = d.back_num from deleted  d

--here is I had trying to print the variable value for kinda debugging but it doesn't work, all the values seems were null or empty
print('prodno '+@ls_prodno+' batchno'+@ls_batchno+' prod_add'+ @ls_prodadd)  
 
return
..........

 

any helps or suggestions will be appreciated .

Accepted Answer
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Friday, 14 June 2024 08:50 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi,

Debug in powerbuilder to find out what INSERt is being done.

Then use SqlS erver Management Studio to run this INSERT having set debug stops in your trigger(s).

https://stackoverflow.com/questions/22033109/how-to-debug-a-t-sql-trigger

 

Comment
  1. Aditya Kabir
  2. Saturday, 15 June 2024 13:51 PM UTC
that's a great idea, thank you so much
  1. Helpful 1
There are no comments made yet.


There are replies in this question but you are not allowed to view the replies from this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.