3

I am trying to generate triggers on a number of tables to force data to upper-case and to trim leading and trailing spaces when data is inserted or updated. The issue I am having is determining in the trigger a general way to tie the INSERTED pseudotable to the base table. Not all of the tables have the same PK name although many do have a unique column called _DataChanges_RowID in each table which can be used. I know that I can inspect the PK and generate teh triggers using those columns, but before I look at that, I wanted to know if there is a generic way to join the INSERTED pseudotable to the base table which is simpler and column agnostic.

DECLARE @TriggerTemplate AS VARCHAR(MAX) = 
'CREATE TRIGGER <<SchemaName>>.<<TriggerName>> ON <<FullTableName>>
AFTER INSERT, UPDATE
AS BEGIN
    IF (ROWCOUNT_BIG() = 0)
        RETURN;
    IF TRIGGER_NESTLEVEL(( SELECT object_id FROM sys.triggers WHERE QUOTENAME(name) = ''<<TriggerName>>'' ), ''AFTER'', ''DML'') < 1
    UPDATE <<FullTableName>>
    SET <<SQLColumnUpdate>>
    WHERE <<FullTableName>>._DataChanges_RowID IN (SELECT _DataChanges_RowID FROM INSERTED);
END;
GO
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''INSERT'';
sp_settriggerorder <<SchemaName>>.<<TriggerName>>, ''FIRST'', ''UPDATE'';
GO
';

WITH cols AS 
    (
        SELECT
            FullTableName = QUOTENAME(S.name) + '.' + QUOTENAME(T.name),
            SchemaName = QUOTENAME(S.name),
            TableName = QUOTENAME(T.name),
            ColumnName = QUOTENAME(C.name)
        FROM
            sys.columns C
            INNER JOIN sys.tables T
                ON C.object_id = T.object_id
            INNER JOIN sys.schemas S
                ON T.schema_id = S.schema_id
        WHERE
            C.is_computed = 0
            AND C.system_type_id IN (
                    167,  -- varchar
                    175,  -- char
                    231,  -- nvarchar
                    239   -- nchar
                )
    )
, TablesAndColumns AS (
    SELECT
        cols.FullTableName
        , cols.TableName
        , cols.SchemaName
        , TriggerName = QUOTENAME('TRG_DATA_CONFORM_' + cols.FullTableName)
        , SQLColumnUpdate = STUFF((SELECT ', ' + c2.ColumnName + ' = UPPER(LTRIM(RTRIM(' + c2.ColumnName + ')))' 
               FROM cols c2
              WHERE c2.FullTableName = cols.FullTableName
              ORDER BY c2.ColumnName
                FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')
    FROM cols
    GROUP BY cols.FullTableName, cols.SchemaName, cols.TableName
)
SELECT FullTableName
    , SQLTrigger = REPLACE(REPLACE(REPLACE(REPLACE(@TriggerTemplate, '<<SQLColumnUpdate>>', SQLColumnUpdate), '<<TriggerName>>', TriggerName), '<<FullTableName>>', FullTableName), '<<SchemaName>>', SchemaName)
    , KeyOK = CASE WHEN EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE QUOTENAME(TABLE_SCHEMA) = SchemaName AND QUOTENAME(TABLE_NAME) = TableName AND COLUMN_NAME = '_DataChanges_RowID') THEN 1 ELSE 0 END
FROM TablesAndColumns
;
4
  • 3
    Personally, I prefer to use a CHECK constraint and tell the developers to fix their code. Nov 1, 2018 at 22:58
  • That would be the idea. However, right now the constraints are causing problems because of poor testing. So the roadmap would be to remove/disable the constraints, ensure that the data stays clean, until the application code can be fixed.
    – Cade Roux
    Nov 1, 2018 at 23:09
  • Did you get a buyout from the developers to do this? And making a generic proc like this almost sounds like a systemic problem...
    – clifton_h
    Nov 2, 2018 at 2:11
  • 1
    @clifton_h There are systemic problems in this system due to the DB designer and the developers. So it does take a systematic approach to fix them and then transition the design of both the database and code to a better future state. My first priority is keeping the customers happy by being able to get their job done while the developers fix their stuff.
    – Cade Roux
    Nov 2, 2018 at 17:06

1 Answer 1

2

No, there is no generic, column-agnostic way to join the INSERTED pseudo-table to the base table. You will need to use a conventional inner join and reference the PK column (or columns if the key is composite) explicitly in the join predicate.

Therefore, if the PK column is not the same in each of the tables you are generating the triggers for, there is no way around querying metadata to find out the column name to use. You can do that using the SQL Standard-compliant (well, more or less) INFORMATION_SCHEMA views (example) or using the native SQL Server system catalogues (example).

1
  • Thanks, that's what I had to end up doing.
    – Cade Roux
    Nov 2, 2018 at 21:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.