Create SQL Server trigger - dynamic SQL too long

Question

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

How you are saying string is truncated? Are you sure `SET @STR_Trig = @STR_Trig +..` this is how you are executing. — Pரதீப், Jan 20 '17 at 16:53
I had seen that method done here answered by martin smith http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits scroll down to How to resolve the problem you are experiencing. — Ryan Gavin, Jan 20 '17 at 16:58
So `SET @STR_Trig = @STR_Trig +..` with this approach string never gets truncated . Don't use `Print` statement to check the dynamic string it will truncate the an show the string. In your case it ill show only up to `4,000` characters — Pரதீப், Jan 20 '17 at 17:03
Are you saying it should get truncated? I am not using print to check the dynamic SQL — Ryan Gavin, Jan 20 '17 at 17:06
No. Am saying your method is perfectly fine. How you are saying this *exec isn't reading the statement completely and cuts the procedure off.* ? Are you getting any error while executing the dynamic query ? — Pரதீப், Jan 20 '17 at 17:08
It cuts of the line i.e SELECT NAME FROM TABLE would be SELECT NAME FROM TA... There is no other issue with it until i go to extend the dynamic SQL query.. — Ryan Gavin, Jan 20 '17 at 17:14
You need to make sure you use the N prefix as well on any string literals 4000-8000 characters as that answer also states. — Martin Smith, Jan 20 '17 at 22:07

score 1 · Accepted Answer · answered Jan 26 '17 at 11:40

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

Create SQL Server trigger - dynamic SQL too long

1 Answers1