unable to update table DB_Main" attachment added  Topic is solved

Support for Microsoft SQL Server in the context of Jiwa installations.

unable to update table DB_Main" attachment added

Postby jak » Thu Feb 23, 2017 7:13 pm

I'm trying to implement a very simple trigger in DB_Main table, in this trigger it will update another table if DB_Main table is updated,

and it works perfectly if i try this trigger in ms sql, but when we update values from jiwa and click on save button, it shows this message

"unable to update table DB_Main" attachment added.

this is my trigger
Code: Select all
create trigger Trigger1 on DB_Main
after update
print 'update';
declare @jid nvarchar(50);
select @jid = inserted.DebtorID from inserted

declare @ano nvarchar(20);
select @ano = inserted.AccountNo from inserted

declare @climit nvarchar(30);
select @climit = inserted.CreditLimit from inserted

print 'jid inserted'+@jid;*/
if update(CreditLimit)
update NewCrtable set CreditLimit = ''+@climit+'' where DebtorId = ''+@jid+'' and AccountNo = ''+@ano+''

can you please let me know how we can run our trigger without getting this message.

Thank you in advance
jiwa error.png
jiwa error.png (9.56 KiB) Viewed 2616 times
Occasional Contributor
Occasional Contributor
Posts: 22
Joined: Mon Oct 10, 2016 10:00 pm
Topics Solved: 0

Re: unable to update table DB_Main" attachment added  Topic is solved

Postby Mike.Sheen » Fri Feb 24, 2017 10:04 am


Put a SET NOCOUNT ON at the start of your trigger to solve the issue.

When we issue our INSERT, UPDATE or DELETE statements and we only expect one row to be affected, then we often throw an exception if the rows affected is <> 1. Your trigger is affecting the rowcount returned, so putting SET NOCOUNT ON will stop that.

I also don't think you should be using nvarchar - we use varchar types and if you ever use a JOIN from an nvarchar to a varchar you're going to see a performance hit.

I also am curious why your creditlimit is an nvarchar and your putting in there DB_Main.CreditLimit - which is a DECIMAL(19,6)?

Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Jiwa Shihan
Jiwa Shihan
Posts: 1681
Joined: Tue Feb 12, 2008 11:12 am
Location: North Sydney
Topics Solved: 524

Re: unable to update table DB_Main" attachment added

Postby pricerc » Sun Nov 12, 2017 8:28 pm

I know this is a fairly old post, but I just noticed it, and thought I'd chip in about the trigger.

I have (ab)used triggers a lot, over many years, and besides Mike's observations about the datatypes and SET NOCOUNT ON, that trigger exhibits a potentially catastrophic problem.

The INSERTED pseudo-table is a TABLE, which can have many records. In other DBMS environments, triggers operate on a single ROW, but SQL Server triggers operate on a SET of ROWS.

So, if you were to update DB_Main (not that I'd recommend this), say to increase everyone's credit limit leading up to Christmas with a
Code: Select all
UDPATE DB_Main SET CrLimit = CrLimit * 1.2

Which debtor's data would end up updated in Jak's NewCrtable is uncertain, although only one record would be updated, not all of them.

That trigger would be better off as a single, simple update statement:
Code: Select all
set nocount on
if update(CreditLimit)
   update NewCrtable set CreditLimit = CrLimit
   from NewCrtable inner join inserted
   on inserted.DebtorId = NewCrtable.DebtorId

with no need for any of the variables in the trigger. And since DebtorId is a primary key, you shouldn't need to use both DebtorId and AccountNo.

ERP Consultant,
Advanced ERP Limited, NZ
User avatar
Jiwa Kohai
Jiwa Kohai
Posts: 233
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 6

Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest