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
as
begin
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)
begin
update NewCrtable set CreditLimit = ''+@climit+'' where DebtorId = ''+@jid+'' and AccountNo = ''+@ano+''
end
end
go


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

Thank you in advance
Attachments
jiwa error.png
jiwa error.png (9.56 KiB) Viewed 7249 times
jak
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

Jak,

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
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
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

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)
begin
   update NewCrtable set CreditLimit = CrLimit
   from NewCrtable inner join inserted
   on inserted.DebtorId = NewCrtable.DebtorId
end


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.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron