Database trigger in SQLServer clears number of updated rows

, ,
1

Hello everybody,

I have made a strange observation with an SQL update adapter that is run against Microsoft SQLServer.

We have a flow that is supposed to update a set of records in the database. Behind the SQL adapter that does this we check the return value with the number of updated rows.

But whatever the real number of rows is the adapter always returns zero. At first glance it looked like an error in the JDBC driver or somewhere else inside IS but further analysis brought to light that a database trigger listening on the table to be updated seems to influence the result. When the trigger is disabled the adapter returns the correct number of rows.

Has anyone encountered the same thing and found a solution to it?

I know that this is rather an SQLServer-specific topic but I have the hope that someone can help me here, also :slight_smile:

When it comes to SQLServer I am a complete n00b. It was enough of a pain to “translate” the triggers from Oracle/DB2, where we created them, to SQLServer :frowning:

Thanks in advance for any hints

Sascha

2

Hi Sascha,
Can you also tell us, what is that update trigger in db doing when an update operation is being performed in table?

Is it a before INSTEAD OF or AFTER update trigger?

-Senthil

3

Hi Senthil,

thank you for your reply.

The trigger is an “AFTER UPDATE”-trigger, which inserts a record in another table when my table in question is updated by the SQL adapter.

The subsequent INSERT in that other table is using a subselect on a third table.

It looks something like this:


USE [myDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[PUSH_UPD] ON [dbo].[POSTBOX]
AFTER UPDATE AS
BEGIN
  IF UPDATE(UserID) 
  BEGIN 
    INSERT INTO PUSH_BUFFER 
      SELECT DISTINCT i.teamID, subscr.address 
      FROM inserted i, subscription subscr 
      WHERE i.userid is NOT NULL AND subscr.mandatorid=i.mandatorid 
  END
END;

This code is a bit simplified. I left out a few columns that are observed for updates and limited it to the column UserID. Also the WHERE-condition in the inner select is in reality somewhat more complicated :slight_smile:

So, if the table POSTBOX is updated, the trigger is supposed to insert a record into the table PUSH_BUFFER from which a service controlled by a scheduler task is reading.

As said, when I disable the trigger in the database, the SQL adapter returns the number of rows it updated. With the trigger activated I get a zero.

Cheers,
Sascha

4

Ah, I have a clue…

It is well apparent that the trigger’s execution belongs to the UPDATE transaction. I believe it is the same like with Oracle or DB2.

But with SQLServer it seems that the trigger being the last in line is the one who returns the value to the SQL adapter. When the trigger is executed successfully that return value would be 0, instead of the number of rows that were affected by the UPDATE.

Can anyone confirm that? And: Does anybody know a workaround or real solution for this?

Thanks,
Sascha