Create a trigger with a stored procedure

• lagouws

  Old Hand

  Points: 308

  More actions

  June 5, 2012 at 3:34 am

  #270243

  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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 5, 2012 at 3:56 am

  #1496949

  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

• lagouws

  Old Hand

  Points: 308

  More actions

  June 5, 2012 at 5:50 am

  #1496985

  Thanks a million Gianluca

  It is working perfect.

  Lukas

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 5, 2012 at 5:58 am

  #1496990

  You're welcome.

  Glad I could help.

  -- Gianluca Sartori

• Joe Clifford

  SSCrazy Eights

  Points: 8739

  More actions

  June 6, 2012 at 12:23 am

  #1497416

  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

• rittwik_rittwik

  Valued Member

  Points: 59

  More actions

  July 13, 2015 at 3:58 pm

  #1812164

  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