Re: How to keep a log register after rollback transaction?

On Wed, 3 May 2006 04:48:02 -0700, Santiago Sanz wrote:

Hello!

I have this transaction:


BEGIN TRANSACTION

INSERT INTO logs (message) VALUES ('SQL will inserts a new register')


INSERT INTO products (id, name) VALUES (1, 'book')

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION


but I want to keep the log message, even if rollback occurs. Do you now can
I do this?

Thanks you very much!!

Santi

Hi Santi,

Easiest is to just move the BEGIN TRANSACTION statement down to the next
line:

INSERT INTO logs (message)
VALUES ('SQL will inserts a new register')

BEGIN TRANSACTION

INSERT INTO products (id, name)
VALUES (1, 'book')

IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION

--
Hugo Kornelis, SQL Server MVP
.

Relevant Pages

  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: DB API 2.0 and transactions
    ... CURRENT_TIMESTAMP within a transaction should be the same. ... manadate that CURRENT_TIMESTAMP in only evaluated once in each SQL ... transaction-initiating SQL statement takes place. ... src = self.__cnx.source ...
    (comp.lang.python)
  • Re: commit work not working?
    ... Because he is using straight sql, ... The transaction is made up of the statements that occur ... locks the stock table, updates rows in the stock table ... The database server must perform this sequence of operations either ...
    (comp.databases.informix)
  • Re: Handling Script Timeout when invoking ActiveX Object involving ADODB from ASP2.0 pages- SQL Clie
    ... The SQL Client Network Utility on the remote SQL Server 2000 SP3a on Windows ... > This is an example of an Aborted Transaction. ...
    (microsoft.public.sqlserver.clients)
  • Re: [fw-wiz] RE: IDS (was: FW appliance comparison)
    ... Logs compress really well, which further reduces your ... a SQL database with indexes on your tables and an interpreted ... > With that much data, and 98% of it being useless, you kind have ... >transaction - no, sorry, PACKET ...
    (Firewall-Wizards)