Reputation: 5
I working on for maintaining a legacy project, my PowerBuilder version is 12.5 and my SQL Server is 2008 R2, I encountered a problem which when I tried to update the records within a datawindow in a PowerBuilder program, it triggered a trigger in a table in a SQL Server which update another table, meanwhile that table has a trigger as well , it went wrong in the second table. that's why I print some variables in the trigger of second table, I'd like to show you the trigger of first table
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
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 update trigger for second table , this isn't working, the problem is prodno and batchno and prodadd is totally all empty or space, it's so strange.
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
print('prodno '+@ls_prodno+' batchno'+@ls_batchno+' prod_add'+ @ls_prodadd) -- I tried to print the values of the variables I need to know but they're all empty, this is strange
return
..........
any suggestions or ideas I'll be appreciated, thank you so much. there's no way for debug a trigger on PowerBuilder
I'm looking forward to seeing in the second trigger, it print the real @prodno @batchno @prodadd as the same value in the first trigger, thank you so much
Upvotes: 0
Views: 72