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. 3 4921
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]
- mv*@spam.guard.caspershouse.com
"ChrisN" <cn*****@hotmail.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl... 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,
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.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:uH*************@TK2MSFTNGP09.phx.gbl... 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] - mv*@spam.guard.caspershouse.com
"ChrisN" <cn*****@hotmail.com> wrote in message news:%2****************@TK2MSFTNGP11.phx.gbl... 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.
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.
"ChrisN" wrote: 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.
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in message news:uH*************@TK2MSFTNGP09.phx.gbl... 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] - mv*@spam.guard.caspershouse.com
"ChrisN" <cn*****@hotmail.com> wrote in message news:%2****************@TK2MSFTNGP11.phx.gbl... 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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Joel Thornton |
last post by:
Whenever something is inserted to a given table, I want to run some
shell commands using xp_cmdshell. Would it be a bad idea to put this
xp_cmdshell in the INSERT trigger of this table?
I...
|
by: Matik |
last post by:
Hello to all,
I have a small question.
I call the SP outer the DB. The procedure deletes some record in table
T1.
The table T1 has a trigger after delete.
This is very importand for me, that...
|
by: Scott CM |
last post by:
I have a multi-part question regarding trigger performance. First of
all, is there performance gain from issuing the following within a
trigger:
SELECT PrimaryKeyColumn FROM INSERTED
opposed...
|
by: robert |
last post by:
typed this into the ibm.com search window, but didn't get anything
that looked like it would answer a question:
+trigger +faster +db2 +cobol
the question: are DB2 (390/v6, at the moment)...
|
by: Dave Sisk |
last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table.
(Note this is an external trigger defined with the ADDPFTRG CL command, not
a SQL trigger defined with the CREATE...
|
by: Bob Stearns |
last post by:
I have two (actually many) dates in a table I want to validate on
insertion. The following works in the case of only one WHEN clause but
fails with two (or more), with the (improper?...
|
by: teddysnips |
last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.
Is there any alternative? I could write it in laborious code...
|
by: Ots |
last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app.
I have an Audit trigger that logs changes to tables. I want to apply
this trigger to many different tables. It's the same trigger,...
|
by: JohnO |
last post by:
Hi All,
This question is related to iSeries V5R4 and db2.
I want to implement an AFTER DELETE trigger to save the deleted rows
to an archive table, I initially defined it as a FOR EACH...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |