Need to do two INSERTs in a Transaction, but...

[organicg]

I'm trying to do two INSERTs as a transaction and the catch is that the auto-generated Identifier, which is the Primary Key of the first table INSERTed into, needs to be a parameter to the 2nd INSERT. After the 1st INSERT I:
SET @TripID = @@IDENTIFER
to retrieve this value, but when I use @TripID in the 2nd INSERT I get an error that this variable is NULL and I'm trying to INSERT a NULL value into a non-NULLable field. I'm thinking that this is because the 1st INSERT has not actually been executed, since it's part of a transaction that has not been committed. How can I solve this problem?

 

[Stamey]

Not that I'm a genius in this area, but I will make a suggestion.
Do the first insert and commit it. If this fails then bypass the second insert altogether. If commit successful, do this: Obtain the identifier from that newly inserted record (do this wherever is convenient). Within a transaction, do the second insert. If the second insert fails, which you will be able to tell by the transaction, roll back the first insert by doing a 'Delete from table where identifier = @Identifier'.
This may be the long way around but should achieve the same result.

Chris

 

[MeanGreen]

There is a forum that is similar to your insert issue: Try this:

thread183-383323

 

[organicg]

OK, I read thread183-383323. So a trigger would work for 'triggering' the 2nd INSERT, but how do I ensure data integrity, that both rows were INSERTed without error. The triggered INSERT could still fail, right? I guess I'm questioning the efficacy of a trigger, in general. Maybe I should just not worry about it? Yeah, right.

 

[MeanGreen]

If any part of the trigger fails, the transaction is rolled by. So I would not worry about it in general.

 

[organicg]

Pardon me for being extra thorough...but we are techies, right?
You're saying the the initial INSERT will be rolled back if the TRIGGER fails to do the 2nd INSERT?

 

[MeanGreen]

Well.... I found this concerning roll backs:

If a ROLLBACK TRANSACTION is issued in a trigger:

All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.


The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.


The statements in the batch after the statement that fired the trigger are not executed.
@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)

ROLLBACK TRANSACTION statements in stored procedures do not affect subsequent statements in the batch that called the procedure; subsequent statements in the batch are executed. ROLLBACK TRANSACTION statements in triggers terminate the batch containing the statement that fired the trigger; subsequent statements in the batch are not executed.

A ROLLBACK TRANSACTION statement does not produce any messages to the user. If warnings are needed in stored procedures or triggers, use the RAISERROR or PRINT statements. RAISERROR is the preferred statement for indicating errors.

So the answer is not always.

 

[organicg]

Well, now I'm more confused. I've only been dealing with stored procs for a few days. Why do you 'CREATE TABLE' in Thread183-383323 ? Does this only try to create the 2 tables when the procedure is run for the first time? I'm asking because currently my procedure uses CREATE PROC to do the two INSERTS, but as we're discussing, that doesn't work. I've already created my two tables in Enterprise Manager so do I skip the CREATE TABLE syntax and just...well, I'm not sure how to do it. Do I need a CREATE PROC procedure and a separate CREATE TRIGGER procedure?

 

[MeanGreen]

NO.... the create tables were to create an environment to show how the trigger works. The code from Create Trigger down is the meat of your trigger.

Good Luck.

 

[organicg]

preciate the help
Thanks a lot MeanGreen