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  4953 
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.co m
"ChrisN" <cn*****@hotmai l.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.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.c om> wrote in
message news:uH******** *****@TK2MSFTNG P09.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.co m
"ChrisN" <cn*****@hotmai l.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.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.c om> wrote in
message news:uH******** *****@TK2MSFTNG P09.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.co m
"ChrisN" <cn*****@hotmai l.com> wrote in message
news:%2******** ********@TK2MSF TNGP11.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 understand that when using xp_cmdshell, the sql thread in question
waits until xp_cmdshell finishes what it's doing. Does this mean if
my xp_cmdshell call takes 30 seconds, that nobody else can insert to
this table until my xp_cmdshell and rest of the...
|
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 the SP will be finished ASAP,
that's why, I do not want, and I do not need to wait for a trigger.
|
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 to:
SELECT * FROM INSERTED
|
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) triggers better
or worse (and how much so, of course) than the same functionality
coded in COBOL? assuming, of course, equal competence in the code.
|
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 TRIGGER statement.) I've also defined
a SQL stored proc, and the trigger is set to call this SP. I've posted the
simplified source below. I can manually call the stored proc, and the
external trigger is created without any errors. However, when I do...
| | |
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? inappropriate?) error message:
SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: CREATE TRIGGER
IS3.date_later_001i NO C;BEGIN-OF-STATEMENT;<space>
which is interpreted as:
An unexpected token "CREATE TRIGGER IS3.date_later_001i NO C" was found
|
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 in the
application, but I'd rather not!
DDL for table and trigger below.
TIA
|
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, with the
exception of the table name. I could manually change the table name in
the trigger and create it, over and over, but I'd like to automate
this - by iterating through the collection of tables and passing the
tablename to something that...
|
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 STATEMENT
trigger that would insert all the deleted rows in one operation like
this:
CREATE TRIGGER MyTable_TD
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
