Table name variable in MS SQL query

Question

I have dynamically created tables, like XXX_JOURNAL. Where XXX - is table prefix (variable), and _JOURNAL - is constant in table name.

I need create UPDATE trigger on database, not on particular table, and use table name (prefix) as variable:

CREATE TRIGGER triggerName ON %_JOURNAL
FOR UPDATE
AS
UPDATE XXX_JOURNAL
SET COMPANY_ID = LEFT(tableName,3) //tableName = current table (XXX_JOURNAL)
WHERE ID = ID FROM inserted

So here I have two difficulties:

1. How to create one trigger for all tables LIKE %_JOURNAL?
2. How to use table name as the keyword for current table?

I know there are a lot of mistakes in syntax. For example, I cannot use '%_JOURNAL' as table name on trigger creation. It's just for explanation, that I need create one trigger for all dynamically created tables in future.

Any ideas?

Answer 1

You can use stored procedure with dynamic SQL:

CREATE PROCEDURE TriggerCreationForJournals
    @XXX as nvarchar(3)
AS
BEGIN

    DECLARE @sql nvarchar(max),
            @triggerName nvarchar(max) = @XXX + N'_JOURNAL_UPDATE',
            @objectCheck int,
            @checkSQL nvarchar(max),
            @params nvarchar(max) = N'@objectCheck int OUTPUT' 

    SELECT @checkSQL = N'SELECT @objectCheck = OBJECT_ID(N'''+@triggerName+''')'

    EXEC sp_executesql @checkSQL, @params, @objectCheck = @objectCheck OUTPUT

    IF @objectCheck IS NULL
    BEGIN
        SELECT @sql = N'
        CREATE TRIGGER '+QUOTENAME(@triggerName)+' ON ['+@XXX+'_JOURNAL]
        FOR UPDATE
        AS
        UPDATE x
        SET COMPANY_ID = '''+@XXX+'''
        FROM ['+@XXX+'_JOURNAL] x
        INNER JOIN inserted i
            ON i.ID = x.ID'

        EXEC sp_executesql @sql
    END
    ELSE
    BEGIN
        PRINT 'Trigger '+QUOTENAME(@triggerName)+' already exists'
    END
END

Then run this:

DECLARE @sql nvarchar(max)

SELECT @sql = (
    SELECT 'EXEC TriggerCreationForJournals '''+LEFT([name],3) +''';' +CHAR(10)
    FROM sys.tables 
    WHERE [name] LIKE '%JOURNAL'
    FOR XML PATH('')
)

EXEC sp_executesql @sql

To create triggers for all tables.

In @sql there will be query like:

EXEC TriggerCreationForJournals 'AFG';
EXEC TriggerCreationForJournals 'DFG';

The purpose of stored procedure is to check if trigger on table exists - if so skip its creation, you can modify the SP to drop them if exists.

The second part is a creation of script and running the SP for all tables you need.

Hope, this answer helps you with your questions.