/*
        This addresses the issue of inserting into other tables in the trigger.
        One restriction is that you can't nest calls to this SP in other triggers like this.
        A cursor is used to store the identity value from the trigger. Then i can fetch from 
        this cursor after the trigger fires in any SP. This is one alternative to using 
        temporary tables. The temporary tables approach can cause performance problems & a
        permanent table approach will require more overhead. Hence this alternative method.

        Create these two SPs - one for setting the identity value from 
        trigger & another for reading it.

*/

----- SPs
CREATE PROC SetIdentityVal
(
@IdentityVal int
)
AS
IF CURSOR_STATUS ('global', 'IdentityVal') >= 0 DEALLOCATE IdentityVal
DECLARE IdentityVal CURSOR FAST_FORWARD FOR SELECT @IdentityVal
GO

CREATE PROC GetIdentityVal
(
@IdentityVal int OUTPUT
)
AS
IF CURSOR_STATUS ('global', 'IdentityVal') < 0
 SELECT @IdentityVal = NULL
ELSE
BEGIN
 FETCH NEXT FROM IdentityVal INTO @IdentityVal
 DEALLOCATE IdentityVal
END
GO
----- SPs

----- Your trigger code:
Create Trigger TABLE1_ITrig On dbo.TABLE1 For Insert
As
DECLARE @ident Integer
SELECT @ident = @@identity
EXEC SetIdentityVal @Ident
--- Other logic here, Inserts into tables without identity columns.
GO

----- Your SP:
CREATE PROCEDURE InsTable1
AS
DECLARE @IdentityVal int
-- Trigger fires that inserts into other tables....
-- Insert Into Table1 Values(....)
EXEC GetIdentityVal @IdentityVal OUT
IF @IdentityVal IS NULL -- Error check
GO

This page was last updated on May 01, 2006 04:28 PM.