CODEWITHSUNDEEP
sqlsql-serverdynamictriggersdynamic-sql
Ryan Gavin
Ryan Gavin

Reputation: 729

Create SQL Server trigger - dynamic SQL too long

Currently I am working on an audit trail using SQL Server triggers to identify inserts, updates and deletes on tables.

Tables can be created dynamically in the database, therefore when this happens I need to create the trigger dynamically.

Therefore at this point I call a stored procedure and pass in the table name.

CREATE PROCEDURE [dbo].[AUDIT_CreateTableTrigger]
    @STR_TableName NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN  
    DECLARE @STR_Trig NVARCHAR(MAX) = ''

    SET @STR_Trig  = @STR_Trig + '
    CREATE TRIGGER [dbo].[' + @STR_TableName + '_Audit] ON [dbo].[' + @STR_TableName + ']
    WITH EXECUTE AS CALLER AFTER
    INSERT, UPDATE, DELETE AS
    BEGIN

    -- do the insert stuff
    -- update
    -- + delete

    END'

    EXEC (@STR_Trig) -- then execute the sql

My issue is that I am noticing that the exec isn't reading the statement completely and cuts the procedure off.

I need a way of executing a long piece of SQL code (I have one solution, this involves splitting the dynamic SQL into 3 triggers i.e insert, update and delete to get around this, however would prefer to keep 1 trigger to handle all)

Any suggestions would be appreciated, Thanks

Upvotes: 0

Views: 1707

Answers (1)

Ryan Gavin
Ryan Gavin

Reputation: 729

Got this issue fixed: Broke up the query see below for solution

DECLARE @sql1 NVARCHAR(4000) = '',
        @sql2 NVARCHAR(4000) = '',
        @sql3 NVARCHAR(MAX)

    SET @sql1  +=  '
    CREATE TRIGGER [dbo].[' + @STR_TableName + '_Audit] ON [dbo].[' + @STR_TableName + ']
    WITH EXECUTE AS CALLER AFTER
    INSERT, UPDATE, DELETE AS
    BEGIN
    BEGIN TRY
    --sql query
    '
     SET @sql2 = '
    --more sql query
    END'

SET @sql3 = CAST(@sql1 AS nvarchar(MAX)) + CAST (@sql2 AS nvarchar(MAX)) 
EXEC sp_executesql @sql3

Upvotes: 1

Related Questions