unable to update table DB_Main" attachment added

[jak]

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

[Mike.Sheen]

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

[pricerc]

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.