Setting conditions on an IF Trigger Statements

  • I am working on a versioning control trigger. I have a flag column which is a bit field which allows content to be shown. I will like to be able to audit anything that is set to live = true on insert and update. If Live = false I do not want to add a column to the Audit table. I would like to always add a column to the audit table if it was a AuditType of Deleted.

    Here is the bottom of my code: How do I add the if statement for deleted? Thanks for your help.

    if(select live from inserted) = 'true'

    BEGIN

    EXEC('INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content, BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)

    SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d ' + @PKCols )

    END

  • I'd have to see the rest of the trigger to tell you the best way to handle this, but you can detect deleted rows in a trigger because they exist in the "deleted" table and don't exist in the "inserted" table.

    Generally speaking, SQL statements work better on Where clauses than on If statements. If you can post the rest of the trigger code, we can probably help turn it into something that will work better.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Hi G Squared, thanks for your prompt response. Here is my entire trigger. Thanks again for your help. I tried some things at the bottom but it is not working.

    USE [XXXX]

    GO

    /****** Object: Trigger [dbo].[Tier1_ChangeTracking] Script Date: 03/11/2010 10:24:41 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER trigger [dbo].[Tier1_ChangeTracking] on [dbo].[Tier1] for insert, update, delete

    as

    declare @AuditType char(1), @PKCols VARCHAR(MAX), @sql VARCHAR(MAX)

    --Find the Primary keys to be used in the inserted and deleted outer join

    select @PKCols = coalesce(@PKCols + ' and', ' on') + ' i.' + c.COLUMN_NAME + ' = d.' + c.COLUMN_NAME

    from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

    where pk.TABLE_NAME = 'Tier1'

    and CONSTRAINT_TYPE = 'PRIMARY KEY'

    and c.TABLE_NAME = pk.TABLE_NAME

    and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

    select * into #ins from inserted

    select * into #del from deleted

    if exists (select * from inserted)

    if exists (select * from deleted)

    SET @AuditType = 'U'

    else

    SET @AuditType = 'I'

    else

    SET @AuditType = 'D'

    --I added this code to prevent multiple inserts by the user trying to get it right

    if exists (select * from inserted) and (select live from inserted) = 1

    BEGIN

    EXEC('INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content, BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)

    SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d ' + @PKCols )

    END

    if exists (select * from deleted)

    BEGIN

    EXEC('INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content, BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic, keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader, oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent, olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)

    SELECT i.*, d.*, ''' + @AuditType + ''' FROM #ins i full outer join #del d ' + @PKCols )

    END

  • I would probably do it more like this:

    ALTER trigger [dbo].[Tier1_ChangeTracking] on [dbo].[Tier1] for insert, update

    as

    INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content,

    BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic,

    keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader,

    oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent,

    olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)

    select i.*, d.*, case

    when i.T1ID is not null and d.T1ID is not null

    then 'U'

    else 'I'

    end as AuditType

    from inserted as i

    full outer join deleted as d

    on i.T1ID = d.T1ID

    where i.live = 1;

    go

    create trigger dbo.Tier1_ChangeTracking_Del on dbo.Tier1 for delete

    as

    INSERT INTO Tier1_AUDIT (T1ID, T1Name, althline, ShortName, class, Alt_text, header, Description, Content,

    BypassUrl, ButtonLoc, FrontPage, Live, allowcontent, datecreated, dateupdated, groupmember, headerpic,

    keywords, tier, oldT1ID, oldT1Name, oldalthline, oldShortName, oldclass, oldAlt_text, oldheader,

    oldDescription, oldContent, oldBypassUrl, oldButtonLoc, oldFrontPage, oldLive, oldallowcontent,

    olddatecreated, olddateupdated, oldgroupmember, oldheaderpic, oldkeywords, oldtier, AuditType)

    select i.*, d.*, 'D' as AuditType

    from inserted as i

    full outer join deleted as d

    on i.T1ID = d.T1ID;

    You could clean up the second one, because "inserted" is always going to have nulls in that case, so why bother querying it.

    I'd also take a look at only auditing the old values. You don't need to record the new values, they're in the Tier1 table already.

    Have you taken a look at the articles I wrote last year for this site, on the subject of Audit Trails and Logging? The articles and their discussions cover the subject pretty extensively.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared, thank you so much for this it is a much better solution. I was using a script which auto generated the triggers and audit tables.

    I will read what you recommended. Can you recommend a good script which can auto generate a soultion like yours?

  • George Murphy (3/11/2010)Can you recommend a good script which can auto generate a soultion like yours?

    Problem would probably be compatibility ... I'm pretty sure GSquared's solution generator only runs on GSquared platform 😀

    Nicely done GSquared

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • The one I use is based on the articles I wrote on the subject. It won't work with your audit table structure.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 1 through 6 (of 6 total)

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