Apparently the return value(s) from executing SQL in SQL Server is/are 'side effects' of certain SQL commands (e.g. INSERT or UPDATE). Activating a trigger during execution of a command, where body the trigger that contains any of these commands, results in more side-effects. In this case these were obscuring the row result of the original SQL command.
The magic cookie is the 'set nocount on/off' SQL Server command which turns the side-effect on and off. It looks like this in a trigger:
create trigger triggerName on tableName as
set nocount on
-- actual trigger commands
-- that may throw row counts
set nocount offDoing this got rid of my problem. I was able to remove the special code from my Perl scripts that double-checked for the result.
So the trigger doesn't return a value per se. The documentation I had not read was the basic SQL Server documentation. I just, y'know, kinda skimmed over that part. Sigh.
Marc M. Adkins wrote:
I added some triggers to an existing SQL Server database. I noticed in testing the triggers using their ad hoc query tool that inserting a row (thus triggering other insertions) would print out:
(0 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
OK, that was interesting. Then I ran my Perl program and inserted a row. The program was working fine before the triggers. Suddenly it kept telling me that the insertion wasn't returning the right number of rows. This was my own error message:
unless ($stmt->execute == 1) { Log->Warn('Insertion of item returned ', $rows, ' rows'); }
I added some code in the unless statement body to double-check: was the row really inserted? This involved checking for the inserted row using a select statement. The row was actually there. Whaddyaknow.
So I'm wondering if SQL Server returns multiple row counts in this type of situation or just the first one. And if the former, whether ODBC understands that. And if so, whether the DBD::ODBC driver understands that. And if so, how would I get the program to understand?
But I'm probably barking up the wrong tree. Some silly misunderstanding on my part. Like the trigger should be returning a value and (d'oh!) it's not.
Which documentation didn't I read?
mma
===========================================================
The usual details:
Windows 2000 SP 4 (?) ------------------ SQL Server 7 ------------------ This is perl, v5.8.4 built for MSWin32-x86-multi-thread (with 3 registered patches, see perl -V for more detail)
Copyright 1987-2004, Larry Wall
Binary build 810 provided by ActiveState Corp. http://www.ActiveState.com
ActiveState is a division of Sophos.
Built Jun 1 2004 11:52:21
------------------
Name: DBI
Version: 1.45
Author: Tim Bunce ([email protected])
Title: DBI
Abstract: Database independent interface for Perl
Available Platforms:
1. MSWin32-x86-multi-thread-5.8
----------------------------------------------------------
Name: DBD-ODBC
Version: 1.11
Author: Jeff Urlwin ([EMAIL PROTECTED])
Title: DBD-ODBC
Abstract: ODBC Driver for DBI
InstDate: 14:42:55 2004
Location:
http://ppm.ActiveState.com/cgibin/PPM/ppmserver-5.8-windows.pl?urn:/PPMServe
r Available Platforms: 1. MSWin32-x86-multi-thread-5.8
