August 10, 2005 at 2:54 pm
Hi.
I'm dealing with a situation where I only have control over the code in a trigger, and not other apps.
When my trigger is added to the application suite, it causes the jdbc rowcount to be reported 'eroneously' (of course this is what it is "supposed" to do per MS design).
I'd like to know if there is any way to restore / preserve the @@rowcount so that the calling application sees how many rows were deleted? It checks rowcount now, and I have no control over anything besides my trigger.
Thanks
August 10, 2005 at 3:01 pm
First thing in the trigger :
Save the rowcount in a variable
SET @rwcnt = @@rowcount
then in the last step of the trigger you can do this :
Update dbo.Numbers set dude = dude where PkNumber 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1), dude bit null, CONSTRAINT Pk_Number PRIMARY KEY CLUSTERED (PkNumber))
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
August 11, 2005 at 3:40 am
Have you experimented with SET NOCOUNT ON in the trigger? I haven't worked with JDBC, so I'm not sure what the impact would be.
August 11, 2005 at 6:04 am
No I haven't. Let me look it over. I'm not sure if I'm going to be allowed to create a table either.
Thanks
August 11, 2005 at 6:20 am
set rowcount looks promising, but I don't see how to restore it at the end of the trigger. Is it possible (I don't want to hose up settings outside the trigger, but leave them as they are/were at trigger entry time)
August 11, 2005 at 6:23 am
My version doesn't touch a thing. Also I used a new table (that should actually be at least created once/server) so that I wouldn't select more data, hence sending another recordset.
Also keep in mind that you can create that table on another db altogether.
August 11, 2005 at 6:46 am
Uhm, am I missing something here? As far as I can test, @@rowcount is not changed in the outside scope by actions done inside a trigger. Test script:
IF OBJECT_ID('dbo.foo') IS NOT NULL
DROP TABLE dbo.foo
GO
IF OBJECT_ID('dbo.bar') IS NOT NULL
DROP TABLE dbo.bar
GO
CREATE TABLE dbo.foo (bar int)
GO
CREATE TABLE dbo.bar (foo int)
GO
CREATE TRIGGER foo_trigger
ON dbo.foo
FOR DELETE
AS
BEGIN
UPDATE dbo.bar SET foo = foo + 1
END
GO
INSERT INTO dbo.bar
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
GO
INSERT INTO dbo.foo
SELECT 1 UNION ALL SELECT 2
GO
DELETE FROM dbo.foo
PRINT 'Rowcount is ' + CAST(@@rowcount AS varchar(10))
SELECT * FROM dbo.bar
The last part deletes 2 rows from dbo.foo, which in turn causes 5 rows to be updated in dbo.bar by the trigger. When rowcount is checked it is still 2 though.
August 11, 2005 at 6:48 am
Oops... so where's the problem then??
August 11, 2005 at 7:10 am
Is JDBC is like ODBC then an extra operation can cause incorrect values to be posted back to the client as each operation returns its own recordset, even empty ones containing just SQL messages. SET NOCOUNT ON int the trigger can prevent this.
August 11, 2005 at 7:57 am
Aha, I thought they were actually checking @@rowcount, but I guess they are just checking the properties of the recordset (or similar object).
August 11, 2005 at 8:25 am
Thanks so much. I'm still testing for side effects etc (the trigger calls some sp's and I want to make sure they still work), but if I add this to the beginning of the trigger:
set nocount on
Then the code which is actually doing the work and has this code in it:
deleteRowCount = pstmt.executeUpdate();
Actually gets a value in deleteRowCount (it was getting zero before, causing some error handling to take effect that I wanted to avoid).
Luckily the "set" commands only affect processing during the trigger execution and revert back when the trigger returns (per MS documentation). I just need now, like I said, to make sure that the sp's the trigger calls don't malfunction with this addition.
Also it works for the delete after trigger.. I need to try the on insert and on update triggers also.
Cheers
Jeff
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply