Re: Advanced - C# SQL Trigger Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Nicholas Paldino [.NET/C# MVP] (mvp_at_spam.guard.caspershouse.com)
Date: 12/01/04 

Date: Wed, 1 Dec 2004 13:50:05 -0500

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]
               - mvp@spam.guard.caspershouse.com
"ChrisN" <cnewald@hotmail.com> wrote in message 
news:%23OL$OQ91EHA.1452@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.
>

Relevant Pages

  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: huge log file when inserting data
    ... Nothing else is running on the database. ... No trigger. ... The insert is in a transaction and it is committed. ... >space requirements. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 7 vs. 2000 issue -trigger and nulls
    ... >We're having trouble with a trigger updating some tables. ... course be locked by the current transaction, ... locking data and updating rows when the COMMIT inside the trigger is ... I seriously hope that SQL Server 7.0 simply disregarded these two ...
    (microsoft.public.sqlserver.mseq)
  • Enable/Disable a database trigger when running a dbunit test
    ... The dabase id's in this database are generated by a trigger. ... the transaction is commited after enabling the trigger: ... data in the database is lost, the testdata is now in place. ...
    (comp.lang.java.help)
  • Re: SQL BEFORE puzzle
    ... trying to figure out the justification for why a BEFORE trigger should ... On a pure SQL perspective, I am going to play devil's advocate... ... subsequent tables before the main commit would occur. ... commits to wait for the main transaction block to commit. ...
    (comp.databases.theory)