The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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

asked 08 Jan '14, 04:31

Giorgio%20Papagno's gravatar image

Giorgio Papagno
3062410
accept rate: 20%

edited 11 Jan '14, 08:00

Graeme%20Perrow's gravatar image

Graeme Perrow
8.4k369107

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

(08 Jan '14, 09:02) Volker Barth

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

(08 Jan '14, 09:24) Giorgio Papagno

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.

(08 Jan '14, 09:45) Breck Carter

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

permanent link

answered 10 Jan '14, 09:29

Giorgio%20Papagno's gravatar image

Giorgio Papagno
3062410
accept rate: 20%

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

permanent link

answered 09 Jan '14, 02:03

Giorgio%20Papagno's gravatar image

Giorgio Papagno
3062410
accept rate: 20%

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.

(09 Jan '14, 10:11) Giorgio Papagno
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×190
×58
×5

question asked: 08 Jan '14, 04:31

question was seen: 961 times

last updated: 11 Jan '14, 08:00