I have two related tables in my database: Page and Tag. One Page can be related to many Tags.

Whenever either of these two tables is modified, a stored procedure called BeforePageHierarchyUpdate needs to be executed (in my case this stored procedure performs some logging and versioning on the Page hierarchy).

What's giving me problems are these two requirements:

1. The SP must be run if either a Page instance or a Tag instance are updated. But if BOTH a Page AND one of its related Tags are updated, the SP should only be called once.

2. The stored procedure must be contained within the same transaction as the other LINQ statements. If the LINQ statements fail, the stored procedure needs to be rolled back. If the stored procedure fails, the LINQ statements must not be executed.

Does anyone have any ideas as to how to implement something like this?

only update these tables using the following procedure:

create procedure UpdatePageAndOrTag
(
    @mode              char(1)  --"P"=page only, "T"=tag only, "B"=both
    ,@pageParam1 ...
    ,@pageParam2 ....
    ....
    ,@TagParam1.....
    ,@TagParam2....
    ....
)

as

EXEC BeforePageHierarchyUpdate

if @Mode="B" or @Mode="P"
Begin
    update Page....
END

IF @Mode="B" or @Mode="T"
Begin
    update tag...
END

return 0
go