Create a trigger with a stored procedure

Question

Post reply

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

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

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 · Answer 2

Thanks a million Gianluca

It is working perfect.

Lukas 🙂

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You're welcome.

Glad I could help.

-- Gianluca Sartori

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 4

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 · Answer 5

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