Advanced - C# SQL Trigger Question

[ChrisN]

Hello all,

I have a quick question. I'm using a C# object to commit new rows to a
database. In the database I have an INSERT Trigger watching values come in.
If the record to be committed fails the trigger's test, the trigger rolls
back the INSERT command and no changes are made to the database.

As far as my object is concerned, the transaction went through either way
(no matter what the trigger did). What I need is for the object to be able
to tell if the trigger has rolled back the new row or not. The trigger
returns an error text using the PRINT command but I can't seem to get that
error using the DataAdapter object (or any objects for that matter). I
could get around this by searching for the newly created record after
committing it but that seems like a bit of an overkill.

Anyway, if anyone has a solution I'd be grateful.

Cheers,
Chris.

 

[Nicholas Paldino [.NET/C# MVP]]

ChrisN,

I don't think that using the print command is a good idea, since
anything can be printed anywhere.

I think that a better idea would be to have the trigger raise an error
that would cause an exception in .NET when executed, and then have the .NET
code handle the transaction management as well. This way, when the trigger
fails, the transaction fails, and you don't have to do anything.

Depending on the scope of the transactions, this might be a good job for
Enterprise Services.

Hope this helps.

 

[ChrisN]

Nicholas,

Thank you for the prompt response. You were absolutely right. I placed a
RAISERROR into the trigger and my existing code picked up on it perfectly.

Thanks again,
Chris.

ChrisN,

I don't think that using the print command is a good idea, since
anything can be printed anywhere.

I think that a better idea would be to have the trigger raise an error
that would cause an exception in .NET when executed, and then have the
.NET code handle the transaction management as well. This way, when the
trigger fails, the transaction fails, and you don't have to do anything.

Depending on the scope of the transactions, this might be a good job
for Enterprise Services.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello all,

I have a quick question. I'm using a C# object to commit new rows to a
database. In the database I have an INSERT Trigger watching values come
in. If the record to be committed fails the trigger's test, the trigger
rolls back the INSERT command and no changes are made to the database.

As far as my object is concerned, the transaction went through either way
(no matter what the trigger did). What I need is for the object to be
able to tell if the trigger has rolled back the new row or not. The
trigger returns an error text using the PRINT command but I can't seem to
get that error using the DataAdapter object (or any objects for that
matter). I could get around this by searching for the newly created
record after committing it but that seems like a bit of an overkill.

Anyway, if anyone has a solution I'd be grateful.

Cheers,
Chris.

 

[Guest]

One reminder about RAISERROR. It its raised with severity less than 11, it
will still give same problematic result as you were getting without raise
error. Severity has to be 11 or higher. Lower severrity used to wrok in
older versions of SQL Servers, I think upto Ver 6.

Harshad.

Nicholas,

Thank you for the prompt response. You were absolutely right. I placed a
RAISERROR into the trigger and my existing code picked up on it perfectly.

Thanks again,
Chris.

ChrisN,

I don't think that using the print command is a good idea, since
anything can be printed anywhere.

I think that a better idea would be to have the trigger raise an error
that would cause an exception in .NET when executed, and then have the
.NET code handle the transaction management as well. This way, when the
trigger fails, the transaction fails, and you don't have to do anything.

Depending on the scope of the transactions, this might be a good job
for Enterprise Services.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Hello all,

I have a quick question. I'm using a C# object to commit new rows to a
database. In the database I have an INSERT Trigger watching values come
in. If the record to be committed fails the trigger's test, the trigger
rolls back the INSERT command and no changes are made to the database.

As far as my object is concerned, the transaction went through either way
(no matter what the trigger did). What I need is for the object to be
able to tell if the trigger has rolled back the new row or not. The
trigger returns an error text using the PRINT command but I can't seem to
get that error using the DataAdapter object (or any objects for that
matter). I could get around this by searching for the newly created
record after committing it but that seems like a bit of an overkill.

Anyway, if anyone has a solution I'd be grateful.

Cheers,
Chris.