Want to create a new transaction

[Andy]

Dear all,

I have created some triggers on tables for audit purposes. I want this
process to be outside of the existing transaction for the application
making the changes to the tables. - if for whatever reason an insert
into the audit tables fails, I do not want the calling application to
fail!

How is this done in TSQL?

This is bound to be simple but its eluding me so far...

Many thanks in advance.
Andy

[David R Rawheiser]

It is simple ... I don't think you can't do it (in SQL).

Transactions in a trigger participate in the open transaction, so you roll
back the outer one you rollback the inner one.

You can invoke an extended (xp_) procedure in the trigger to write a log
outside of the outer transaction.
XP's are really just an call to an external program that runs outside of the
sqlserver (as well as outside the transaction).

If you can't write your own XP DLL , you can invoke an EXE via the standard
xp_cmdshell.

XP's may be turned off for security reasons.

Also since an errant XP could lock up the database server (requireing a
reboot), they should be used with care.
"Andy" <an*********@hotmail.com> wrote in message
news:8f**************************@posting.google.c om...

Dear all,

I have created some triggers on tables for audit purposes. I want this
process to be outside of the existing transaction for the application
making the changes to the tables. - if for whatever reason an insert
into the audit tables fails, I do not want the calling application to
fail!

How is this done in TSQL?

This is bound to be simple but its eluding me so far...

Many thanks in advance.
Andy

[David R Rawheiser]

Reread you question again and saw you wanted the inner one to be rolled
back.

I haven't tried it since it didn't work well in previous version, but by
naming your inner transaction you can do an rollback to the named checkpoint
and continue on.

This is moot if the insert is failing implicitly (via SQL Server) and not
through an explicit rollback.

What's the problem your getting with your audit trail?

"David R Rawheiser" <ra*******@hotmail.com> wrote in message
news:YZ********************@comcast.com...

It is simple ... I don't think you can't do it (in SQL).

Transactions in a trigger participate in the open transaction, so you roll
back the outer one you rollback the inner one.

You can invoke an extended (xp_) procedure in the trigger to write a log
outside of the outer transaction.
XP's are really just an call to an external program that runs outside of the sqlserver (as well as outside the transaction).

If you can't write your own XP DLL , you can invoke an EXE via the standard xp_cmdshell.

XP's may be turned off for security reasons.

Also since an errant XP could lock up the database server (requireing a
reboot), they should be used with care.
"Andy" <an*********@hotmail.com> wrote in message
news:8f**************************@posting.google.c om...

Dear all,

I have created some triggers on tables for audit purposes. I want this
process to be outside of the existing transaction for the application
making the changes to the tables. - if for whatever reason an insert
into the audit tables fails, I do not want the calling application to
fail!

How is this done in TSQL?

This is bound to be simple but its eluding me so far...

Many thanks in advance.
Andy

[Cobra Pilot]

Look at BOL under 'triggers, ROLLBACK TRANSACTION'

You must use the SAVE TRANSACTION statement to do a partial rollback in a
trigger, even if it is always called in autocommit mode. This is illustrated
by the following trigger:

CREATE TRIGGER TestTrig ON TestTab FOR UPDATE AS
SAVE TRANSACTION MyName
INSERT INTO TestAudit
SELECT * FROM inserted
IF (@@error <> 0)
BEGIN
ROLLBACK TRANSACTION MyName
END
Gary"Andy" <an*********@hotmail.com> wrote in message
news:8f**************************@posting.google.c om...> Dear all,

I have created some triggers on tables for audit purposes. I want this
process to be outside of the existing transaction for the application
making the changes to the tables. - if for whatever reason an insert
into the audit tables fails, I do not want the calling application to
fail!

How is this done in TSQL?

This is bound to be simple but its eluding me so far...

Many thanks in advance.
Andy

[Oscar Santiesteban Jr.]

check out www.redmatrix.com and their product SQL Audit

Oscar...

"Andy" <an*********@hotmail.com> wrote in message
news:8f**************************@posting.google.c om...

Dear all,

I have created some triggers on tables for audit purposes. I want this
process to be outside of the existing transaction for the application
making the changes to the tables. - if for whatever reason an insert
into the audit tables fails, I do not want the calling application to
fail!

How is this done in TSQL?

This is bound to be simple but its eluding me so far...

Many thanks in advance.
Andy