CODEWITHSUNDEEP
c#sql.netsql-servertriggers
Teddy Black
Teddy Black

Reputation: 193

SQL know what statement activated a trigger if the trigger fails

I'm using SQL Server in a C# application, and I'm using batch statements. The problem I'm having is in the case where the statement fails, for whatever reason, I need to know which statement failed and return it.

I've managed to do this my putting a try catch around the SQL statement (so it won't quit if it fails) then the C# cmd.ExecuteQuery returns the number of rows affected.

The problem I'm having now is that if the failure occurs because of a trigger, the trigger is outside of the try catch of the original batch and thus won't be caught, but it will instead be caught by the C# application. At this point, the state of the batch is lost in limbo and the whole thing fails.

I need some way of knowing which statement (or the number of rows affected so far, or the index in the batch) triggered the trigger that caused the exception. Whether there's something I can do in SQL or C#, I'm open to whatever.

Thanks.

Upvotes: 0

Views: 1404

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

This isn't an answer but it's far too big for a comment and it need formatting.

say I have a statement like BEGIN TRY INSERT INTO JAZZHANDS VALUES (2) END TRY BEGIN CATCH END CATCH. If the table JAZZHANDS has a trigger that inserts whatever in some other table, if that trigger fails, it won't cycle back and be caught in the CATCH from the original insert. The transaction just fails altogether

I have no idea why you think that. The following SQL:

create table JazzHands (ID int not null,constraint CK_ID_JH CHECK (ID < 10))
go
create table JazzHands2 (ID int not null,constraint CK_ID_JH2 CHECK (ID < 5))
go
create trigger T_JazzHands on JazzHands after insert
as
    insert into JazzHands2(ID)
    select ID from inserted
go
--1
begin try
    insert into JazzHands (ID) values (11)
    PRINT 'No error 1'
end try
begin catch
    Print 'error 1'
end catch
go
--2
begin try
    insert into JazzHands (ID) values (6)
    PRINT 'No error 2'
end try
begin catch
    Print 'error 2'
end catch
go
--3
begin try
    insert into JazzHands (ID) values (1)
    PRINT 'No error 3'
end try
begin catch
    Print 'error 3'
end catch
go

Produces:

(0 row(s) affected)
error 1

(0 row(s) affected)

(0 row(s) affected)
error 2

(1 row(s) affected)

(1 row(s) affected)
No error 3

And both tables contain a single row. That, is, contrary to your assertion, when either something within the table itself prevents the insert from succeeding, or something within the trigger on the table prevents the insert from succeeding, exactly the same error handling occurs, and it's generally indistinguishable.

If you do have an actual example where the failure of a trigger produces different behaviour than failure directly on the table, please edit such an example into your question.

Upvotes: 1

dean
dean

Reputation: 10098

What you're looking for is the last statement, right? You can get this information from inside the trigger by calling DBCC INPUTBUFFER.

See also if this could help:

SELECT session_id, TEXT
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST

Upvotes: 0

Related Questions