how to encapsulate the same code block with inserted vs deleted in a trigger.

  • snomadj

    SSCarpal Tunnel

    Points: 4237

    So I have an IUD trigger on a table with quite a few columns.  (Don't ask about normalisation that's another issue).

     

    My code is …

    INSERT INTO auditTable (a,b,c … k)

    SELECT a,b,c...k FROM inserted

     

    Later I repeat the exact same for the table deleted.

     

    I was trying to think of a way to avoid repeating the same code block with just a different table name (inserted / deleted).  There's nothing wrong with it, it's just that it's a repeat of a (very nearly) duplicate chunk but I can't see a better way.  If anyone can, I'd be grateful for your ideas.

     

    Thanks

  • Lowell

    SSC Guru

    Points: 323331

     

    auditing inserts is "excessively repetitively redundant", i would only audit updates and deletes, as you have the original data in the table, if no updates occurred. do you REALLY need two copies of every row, plus updates and deletes?

    anyway. i think what you want to do is to use a query to generate your trigger for each table.

    since the columns of each table vary, you want to use metadata to generate statements.

    for example this gives a concatenated list of columns.

    SELECT DISTINCT

    t.name,

    sq.Columns

    FROM sys.tables t

    JOIN (

    SELECT OBJECT_ID,

    Columns = STUFF((SELECT ',' + QUOTENAME(name)

    FROM sys.columns sc

    WHERE sc.object_id = s.object_id

    FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

    FROM sys.columns s

    ) sq ON t.object_id = sq.object_id

     

     

    This code is a half completed example of generating a trigger example

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    GO

    WITH MyCTE

    AS

    (

    SELECT DISTINCT

    SCHEMA_NAME(schema_id) AS SchemaName,

    t.name AS TableName,

    sq.Columns

    FROM sys.tables t

    JOIN (

    SELECT OBJECT_ID,

    Columns = STUFF((SELECT ',' + QUOTENAME(name)

    FROM sys.columns sc

    WHERE sc.object_id = s.object_id

    FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')

    FROM sys.columns s

    ) sq ON t.object_id = sq.object_id

    )

    SELECT

    'CREATE TRIGGER TR_'

    + TableName

    + '_IUD ON '

    + QUOTENAME(SchemaName) +'.' + TableName

    + '

    FOR INSERT,UPDATE,DELETE

    AS

    INSERT INTO [Audit].' + TableName

    + '(' + + ')

    SELECT ' + Columns + '

    FROM INSERTED'

    FROM MyCTE

    • This reply was modified 2 weeks, 3 days ago by  Lowell.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • ScottPletcher

    SSC Guru

    Points: 97941

    There's really not a better way.  You'll need to repeat the column list for both the inserted and deleted tables for any method you use.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

  • snomadj

    SSCarpal Tunnel

    Points: 4237

    Thanks Scott.  That was the crux of the question.  It seems inelegant repeating the same code block so I just wanted to be sure I hadn't missed a trick.

    Thanks Lowell.  Good point I hadn't considered about auditing inserts.  And useful code if I need to gen a series of triggers in the future.

    Appreciate you both taking the time!

     

    🙂

  • schleep

    SSChampion

    Points: 12506

    If you need to track the date of an insert, and the table doesn't already have a field from which that can be derived, that's a case for Insert trigger.

    As for combining the code into a single statement, you could do a UNION ALL:

    Insert AuditTable(....)

    SELECT ....

    FROM inserted

    UNION ALL

    SELECT ....

    FROM deleted?

    Of course, this gives you two rows for an update.

     

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

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