First trigger I have ever written seems innocent to me, but results in this error (seems to occur on the SELECT statement inside the trigger):

ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 63 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionWrite (send()).
Server: Msg 11, Level 16, State 1, Line 0
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.

Here is the source for the trigger itself:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


ALTER   TRIGGER AuditLogIsMonitored on dbo.Audit_Log
	AFTER INSERT
AS
declare @status 	int
declare @userid 	varchar(254)
declare @eventid	int
declare @eventP1	varchar(254)
select 
  @status 	= I.AL_STATUS 
, @userid 	= I.AL_USER 
, @eventid	= I.AL_EVENT_ID
, @eventP1	= I.AL_EVENT_PARAM1
FROM INSERTED I 
-- Case 1: 	Login Failure
If @eventid = 8 and @status = 16386 		-- and @userid = '(Login failed)'; this test not really needed
begin
	Execute dbo.CBMI_Audit_LogonFailure @UserName = @eventP1
	RETURN
end
-- Case 2: 	Login Success
If @eventid = 8 and @status = 0  			-- @userid = id of person logging on successfully
begin
	Execute dbo.CBMI_Audit_LogonSuccess @UserName = @userid
	RETURN
end



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

The above error happened on my production system which runs SQL 2000 SP3. So I copied the database to my test system which runs SQL 2000 SP4 

and tried the same thing. I got a similar failure but there were slight differences:

ODBC: Msg 0, Level 19, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 60 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite (WrapperWrite()).
Server: Msg 11, Level 16, State 1, Line 0

[Microsoft][ODBC SQL Server Driver][Shared Memory]General network error. Check your network documentation.
 

I would look for the cause in the CBMI_Audit procedures - this trigger should be completely harmless in itself.

As you probably already figured out, you need to loop through the inserted snapshot row-by-row. If more than one row was inserted, you're only reading whatever happens to be the last row read from that snapshot.

EDIT
The ODBC messages are returned to your client application, which is obviously connecting over ODBC.