Insert and Delete Triggers from only one transaction 1

[yakata]

Hi there
Just working on a tool that will capture some events in the DB(sql server 2000) of one of our supplier to launch some process (restore of files, for ex.) in one of our internally developped tool...
The case is this one : i've a trigger for insert on a table, a trigger for update and one for delete.

Sometimes, the 'off the shelf' application does stranges things as, in only one transaction, inserting a row and immedialtely deleting it...

My problem : as triggers are working at transaction level (am i right ?), the effect is that only the insert trigger fires. I can't capture the delete instruction...(((

I've tried to do something like this in the insert trigger

set @IDInsert=@@identity from inserted
set @IDdelete=@@identity from deleted

but @IDdelete is null...

Any idea ?

TIA

 

[SamGarland]

The deleted table stores copies of the affected rows during DELETE and UPDATE statements.

The inserted table stores copies of the affected rows during INSERT and UPDATE statements.

The deleted table is not used during inserts.

Try checking the deleted table in your delete and update triggers and the inserted table in your insert and update triggers to find affected rows.

Each trigger operates within the same transaction that the statement that caused the delete, insert or update is in.

If within one transaction a row is inserted into the table then a row is deleted from the table, then the transaction is committed the insert trigger will fire, then the delete trigger will fire and there will be no evident change to the table itself since the new row was deleted. If within one transaction a row is inserted into the table then a row is deleted from the table, then the transaction is rolled back, the actions performed by the triggers will be rolled back and there will be no change to the table with the triggers so it may appear that nothing happened (the system is left in the state it was in before the transaction).

 

[yakata]

Thanks SamGarland !
Appreciate your explanation, it helps me to understand that the insert trigger on the table can't never give me infos on the deleted table...
But it doesn't help me to solve the issue immedialtely... Let me be more precise...
First of all, my triggers are sending message using MessageQueuing. I receive infos like IDs of the lines 'changed' in the table
In the table concerned, there is an auto increment identity column...
I put a Insert trigger :
CREATE TRIGGER MM_INSERT_Edit ON [dbo].[Edit]
FOR INSERT
AS

declare @ID varchar(9) ,
@IdProject numeric ,
@Body varchar(2000),
@Subject varchar(100)

select @ID=@@identity from inserted
select @IdProject = IdProject from inserted

set @Subject='Insert in Table : ' + @ID
set @Body='[IdProject]' + convert(varchar,@IdProject)

exec master..xp_msmq_send 'DIRECT=OS:BamBam\private$\mm', @Subject, @Body

Then another for Delete

CREATE TRIGGER MM_DELETE_Edit ON [dbo].[Edit]
FOR DELETE
AS

declare @ID varchar(9) ,
@IdEdit varchar(9),
@Body varchar(2000),
@Subject varchar(100)

declare CurD cursor for select [Id] , IdEdit from deleted

open CurD
set @Body=''

Fetch Next From CurD into @Id, @IdMedia

WHILE (@@FETCH_STATUS <> -1)
BEGIN
set @Body=@Body + '[' + @IdEdit + ']'
Fetch Next From CurD into @Id, @IdMedia

end
Close CurD
DEALLOCATE CurD


set @Subject='Deleted in Table '

exec master..xp_msmq_send 'DIRECT=OS:Bambam\private$\mm', @Subject, @Body


Those two triggers run smooth each one individually...
But it occurs that the trigger of insert fires, giving me an incremented ID (102 for example ,as if we have inserted a new line), the content of the table does not show a new line(last ID is 101) and the delete trigger doesn't fire... That's my problem, I can't figure out if this line exists or not in the table, or I have to poll the table and that's what I don't want to do...
May be the good idea from you is a rollback... but, if a rollback occurs, does the incremental identifier is incremented ? Becasue if after the described situation, I really insert a line, the ID has jumped one value (from 101 to 103)...
TIA for your expert's opinion

 

[SamGarland]

Once an identity value has been used it will not be reused again, so deletions can mean that the numbers in identity columns are not contiguous.

If there was an insertion and then the transaction was rolled back the side effects of the insert trigger which are not logged database actions (such as sending a message) would not be rolled back since they cannot be by definition. The identity value of the previous successfully inserted row would not be used again.

This would explain the symptoms you describe.