Post reply

multiple triggers on a table

  • October 6, 2008 at 7:57 am

    #199303

    I have a table called AlertLog which contains these columns:

    1. AlertCode

    2. TimeStamp

    3. AlertNotified

    4. Resource

    5. AlertMediaCode

    6. AlertStatusCode

    I need to create triggers that fire on insert but each trigger is dependant on the type of AlertCode that has been inserted. AlertCodes start are 100, 101, 102, 103.

    If AlertCode is 100 then send db_mail with email body describing 100 as alert_100

    if AlertCode is 101 then send db_mail with email body describing 101 as alert_101

    I have 2 jobs that run every 10 minutes checking certain conditions and insert into AlertLog when needed.

    can anyone help with this issue?

    how do i get the AlertCode value in order to fire a particular trigger...

  • October 6, 2008 at 9:42 am

    #881172

    You can't programatically control what triggers fire.

    Write a single trigger that has logic to be able to send the different types of mail. It doesn't really sound complex. Bear in mind that if multiple rows are inserted, the trigger will only fire once.

    The rows that were added to the table will be in the inserted pseudo-table that's only visible in the trigger.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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