Results 1 to 5 of 5
  1. #1
    Join Date
    May 2011
    Posts
    36

    Unanswered: Trigger on xml column

    Hi Gurus,

    We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular value get inserted in a tag.

    For ex:

    <File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">

    In the above xml data if we have the value 21545 in Stab the trigger needs to be executed and mail needs to be sent to a distribution list.

    The Trigger should not be initiated if value other than 21545 is updated

    Please help me on this.

    Thanks,
    Ganesh

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Yes it is possible, but I would not do it. The email operation becomes part of your transaction, and that can be an expensive operation to hold open your tansaction for. I would rather have a job poll the data at some interval.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2011
    Posts
    36
    Hi Thrasymachus

    "I would rather have a job poll the data at some interval"


    Could you please explain more on the above quote and give me an Idea or example how can I implement this to get an alret message - as that value 21545 has get inserted in that parameter.

    Thanks,
    Ganesh

  4. #4
    Join Date
    May 2011
    Posts
    36
    Hi Thrasymachus

    Quote Originally Posted by Thrasymachus View Post

    "I would rather have a job poll the data at some interval"
    Could you please explain more on the above quote and give me an Idea or example how can I implement this to get an alret message - as that value 21545 has get inserted in that parameter.

    Thanks,
    Ganesh

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    As Thrasy stated; triggers for this would be a bad idea.

    Instead create a SQL job that queries your tables looking for this value. When found ping off your email(s).
    Set the interval that the job runs to be every N minutes.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •