@@rowcount problem

[Former Member]

I have a strange behaviour with ASA 16. We just migrate from ASA 9.0.2 to ASA 16 ( latest EBF ) and we have two applications, both connecting through ODBC. The first written in Powerbuilder 11.5 when insert records in a table, fires trigger and @@rowcount variable in the trigger has the value 1 ( correct ). The second application written in Visual basic 6 when insert records in a table, fires trigger and @@rowcount variable in the trigger has the value 0 ( wrong ). What could be the reason ?

This is my trigger:

ALTER TRIGGER "t_riga_documento_ai"
on dba.riga_documento
for insert
as
begin

declare @numrows  int,
        @numnull  int

select  @numrows = @@rowcount
if @numrows = 0
return

The problem seems to be a connection level properties .

Thanks in advance

[VolkerBarth]

Is that the whole body of this T-SQL trigger? At least an END is missing, I would suspect.

If this is the whole body, why do you use a trigger at all - you could query @@ROWCOUNT just after the INSERT directly?

If not, it's difficult to tell about possible different behaviour which might be due to different connection-level settings...

[Former Member]

Hi Volker, it's not the entire trigger but i'm sure it works perfectly. The code you see was produced by Powerdesigner many years ago. But the strange behaviour is that from Powerbuilder code it works ( and work from many years and different ASA versions ) and from VB6 it doesn't... ( but in ASA 9.0.2 it worked ) Thanks

[Breck_Carter]

FWIW it defaults to an AFTER - FOR EACH STATEMENT trigger. Is there another trigger on the same table that fires before this one?

Does the VB connection set "auto commit" differently from the PB connection?

...it does smell like a bug, just trying to narrow down the exact path.

[Former Member]

I solved !!! The problem was in OLEDB ADO connection. Using CursorLocation = adUseServer ( or default = adUseServer ) it happens that @@rowcount variable, tested inside trigger, assumes zero value.

Changing CursorLocation = adUseClient all works fine.

However it's a ASA 16's behaviour change because same connection made with ASA 9.0.2 worked well,while in ASA 16 have problem.

I hope this experience can help you for the future.

Bye

[Former Member]

Hi Breck, I've verified and both connections ( from Powerbuilder and from VB6 ) have chained = ON so autocommit is false. There aren't other trigger on the same table before this one, and the trigger is a standard after - for each statement I'm thinking to a bug... Thanks

[Former Member]

I add a new test result from my developers. In VB6 triggers work fine if they execute a direct statemente like:

Connection.Execute ("delete from DBA.table WHERE tableid = X")

Instead doesn't work when they write something like:

... set o variable per row and then

Grid.Update()

They connect using OLEDB but they dont' use SAOLEDB provider but a generic MSDASQL provider.