Create a trigger with a stored procedure

  • Hi

    I am trying to create a trigger with in a stored procedure. When I execute the stored procedure I am getting the following error :

    Msg 2108, Level 15, State 1, Procedure JPDSAD1, Line 1

    Cannot create trigger on 'FRIT_MIP003_BOK_BTCH_LG.P62XB1.XB1PDS' as the target is not in the current database.

    Here is the code for the stored procedure :

    CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))

    AS

    BEGIN

    exec ('USE ['+@databaseA+'];')

    exec ('CREATE TRIGGER [P62XB1].[JPDSAD1] ON [' + @databaseA + '].[P62XB1].[XB1PDS] ' +

    'AFTER DELETE AS ' +

    'BEGIN ' +

    ' INSERT INTO [' + @databaseA + '].[P62XB1].[XL1TDS] SELECT CAST(SYSDATETIME() AS DATETIME2(6)) , ''B'' , ''D'' , IDA_DELETE ' +

    ' ''0001-01-01 00:00:00.000000'' , '' '' FROM DELETED ' +

    'END')

    END

    Thanks

    Lukas

  • Try this instead:

    CREATE PROCEDURE [dbo].[InsertTRIGGER](@databaseA varchar(50))

    AS

    BEGIN

    DECLARE

    @sql nvarchar(max),

    @statement nvarchar(max)

    SET @statement = '

    CREATE TRIGGER [P62XB1].[JPDSAD1] ON [P62XB1].[XB1PDS]

    AFTER DELETE AS

    BEGIN

    INSERT INTO [P62XB1].[XL1TDS]

    SELECT CAST(SYSDATETIME() AS DATETIME2(6))

    , ''B''

    , ''D''

    , IDA_DELETE

    , ''0001-01-01 00:00:00.000000''

    , '' ''

    FROM DELETED

    END

    '

    SET @sql =

    'EXEC ' + QUOTENAME(@databaseA) + '.sys.sp_executesql

    N''EXEC(@statement)''

    , N''@statement nvarchar(max)''

    , @statement;'

    EXEC sp_executeSQL @sql, N'@statement nvarchar(max)', @statement

    END

    Invoking sp_executesql from the target database is a quick way to work around this kind of restriction.

    -- Gianluca Sartori

  • Thanks a million Gianluca

    It is working perfect.

    Lukas 🙂

  • You're welcome.

    Glad I could help.

    -- Gianluca Sartori

  • Dare I ask why we're using a stored proc to dynamically create a trigger? Triggers/contraints/foreign keys, etc. should be constant, not defined on the fly...

    Joe

  • How can I create that trigger inside the sp where we don't know that how much colmns are present in the table? May be the table should be created by client

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply