-
Aditya Kabir
- PowerBuilder
- Wednesday, 5 August 2026 04:56 AM UTC
Hi, I work on a legacy project. It used to be maintained with PowerBuilder 6, but recently we found it doesn't support certain characters, so my company decided to upgrade it. It just happened that our server's motherboard was damaged, so we had to replace our server as well. We bought a new server, but we didn't upgrade our database software for compatibility reasons. we still use SQL Server 2008 R2.
Now that I have upgraded our application from PowerBuilder 6 to PowerBuilder 12.5, I have found an issue. We have a table for recording sold goods information, and it uses a trigger to deduct inventory stock. This trigger has worked fine for the last decade until we upgraded PowerBuilder and changed our server. It sometimes works abnormally and does not deduct the inventory stock.
Here is the structure of the selling goods table:
CREATE TABLE [dbo].[inv_sub](
[list_no] [dbo].[char](10) NOT NULL,
[prod_no] [dbo].[char](8) NOT NULL,
[prod_add] [dbo].[char](12)NOT NULL DEFAULT (''),
[batch_no] [dbo].[char](12) NOT NULL DEFAULT (''),
[inv_num] [numeric](12, 4) NOT NULL,
[had_back_num] [numeric](12, 4) NOT NULL DEFAULT (0),
[std_price] [dbo].[prod_pri_type] NOT NULL,
[inv_per] [numeric](5, 2) NOT NULL DEFAULT (100),
[sell_price] [dbo].[prod_pri_type] NOT NULL DEFAULT (0),
[lest_num] [numeric](12, 4) NOT NULL DEFAULT (0),
[avail_date] [datetime] NULL,
[last_rebate] [numeric](8, 2) NULL DEFAULT (0),
[row_index] [numeric](16, 0) IDENTITY(1,1) NOT NULL,
[retail_price] [numeric](8, 2) NULL DEFAULT (0),
[dep_price] [numeric](12, 6) NULL DEFAULT (0),
[produce_date] [datetime] NULL,
[leter_jgfs] [varchar](2) NOT NULL DEFAULT (''),
[in_bz] [char](1) NOT NULL DEFAULT ('0'),
[cgr_no] [char](2) NULL,
[cgr2_no] [char](2) NULL,
[ckfh] [char](1) NOT NULL DEFAULT ('1'),
[ck_date] [datetime] NOT NULL DEFAULT (getdate()),
[bjg_num] [numeric](9, 3) NULL DEFAULT ('0'),
[sms_zt] [varchar](6) NULL DEFAULT ('passed'),
[mj_no] [dbo].[batch_no_type] NOT NULL DEFAULT (''),
[fhjl] [varchar](6) NULL DEFAULT ('passed'),
[had_check] [char](1) NULL DEFAULT ((0)),
[zh_fh_no] [char](2) NULL,
[zh_zh_no] [char](2) NULL,
[zh_fh_no2] [char](2) NULL,
[flag] [varchar](5) NOT NULL DEFAULT ('00'),
[last_add2] [varchar](50) NULL,
[batch_no_all] [char](50) NULL,
[prod_add_all] [char](50) NULL,
[remark] [char](1) NULL,
[rmyy_monad] [char](4) NULL,
[rmyy_price] [numeric](8, 2) NULL,
[rmyy_num] [numeric](9, 3) NULL,
[box_num2] [decimal](18, 3) NULL,
[box_num] [char](20) NULL,
[prod_add_code] [char](15) NULL,
CONSTRAINT [PK__inv_sub__6D0D32F4] PRIMARY KEY NONCLUSTERED
(
[list_no] ASC,
[prod_no] ASC,
[prod_add] ASC,
[batch_no] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [FK__inv_sub__list_no__76969D2E] FOREIGN KEY([list_no])
REFERENCES [dbo].[inv_main] ([list_no]),
CONSTRAINT [FK__inv_sub__prod_no__778AC167] FOREIGN KEY([prod_no])
REFERENCES [dbo].[product] ([prod_no]),
CHECK (([had_back_num] <= [inv_num]))
) ON [PRIMARY]
GO
here is the trigger for insert:
ALTER trigger [dbo].[inv_sub_Insert]
on [dbo].[inv_sub] for insert
as
if (select count(*) from lock_trigger where user_spid = @@spid and table_name in ('all','inv_sub') ) > 0 return
declare @ln_lestnum numeric(12,4)
declare @ln_invnum numeric(12,4)
declare @ls_groupno char(2)
select @ln_invnum = inserted.inv_num from inserted
select @ln_lestnum = prod_dep.Lest_num
from inserted ,prod_dep
where prod_dep.prod_no = inserted.prod_no and
prod_dep.prod_add = inserted.prod_add and
prod_dep.batch_no = inserted.batch_no
if @@rowcount = 0
begin
print('query inventory data encountered error!')
return
end
if @ln_invnum > @ln_lestnum
begin
print('it doesn't have sufficient inventory stock to sell!')
return
end
if @ln_invnum = @ln_lestnum
begin
delete prod_dep from inserted, prod_dep
where prod_dep.prod_no = inserted.prod_no and
prod_dep.prod_add = inserted.prod_add and
prod_dep.batch_no = inserted.batch_no
if @@rowcount = 0
begin
print('delete invetory stocks encountered error!')
return
end
end
else
begin
update prod_dep
set prod_dep.Lest_num = prod_dep.lest_num - inserted.inv_num
from inserted
where prod_dep.prod_no = inserted.prod_no and
prod_dep.prod_add = inserted.prod_add and
prod_dep.batch_no = inserted.batch_no
if @@rowcount = 0
begin
print('update inventory stock encountered error!')
return
end
end
update c set lately_rebate = i.sell_price, inv_date = m.inv_date, last_inv_num = i.inv_num
from inv_main m,inserted i,cli_rebate c
where m.list_no = i.list_no and m.cli_no = c.cli_no and i.prod_no = c.prod_no
if @@rowcount=0
begin
insert into cli_rebate
select m.cli_no, i.prod_no, i.sell_price, null, m.inv_date, i.inv_num
from inserted i,inv_main m where m.list_no=i.list_no
if @@rowcount=0
begin
print('update client price encountered error!')
return
end
end
here below is my inventory stocks table
CREATE TABLE [dbo].[prod_dep](
[prod_no] [dbo].[char](8) NOT NULL,
[prod_add][dbo].[char](12) NOT NULL,
[batch_no] [dbo].[char](12) NOT NULL,
[lest_num] [numeric](12, 4) NOT NULL,
[Box_Num] [char](20) NULL,
[avail_date] [datetime] NULL,
[produce_date] [datetime] NULL,
PRIMARY KEY NONCLUSTERED
(
[prod_no] ASC,
[prod_add] ASC,
[batch_no] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
any suggestions will be appericated
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.