Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2005
    Posts
    40

    Unanswered: DDL and DML Triggers and @query

    I have a DDL trigger that works great-it inserts data into a table called LogEvents. I have a DML trigger that fires a database email on an insert to this same table. All this will work fine. However, when I use an @query in the database email send that references this same table of these two triggers a block occurs, appears to be a deadlock.

    DDL trigger works fine. How do I design a DML trigger to send an email with the @query option?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In your DML trigger - are you referencing INSERTED when using @query to parse the contents of the XML field?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2005
    Posts
    40
    yes. Seems like a circular reference. On an insert I would like the database email sent. What is the way to do this?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    In this case deadlock is very likely. You're virtually taking the contents of transaction log and passing them "by reference" outside of SQL Server name space. What you may want to do is to store the contents of inserted into a #temptable, then iterate through it by capturing the XML value per row into a memvar, and only then going out to your mailsend routine. Personally, I don't like this mailsend because it has to be done through xp_cmdshell. Have you tried CDO yet?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Apr 2005
    Posts
    40
    You method would work. I chose to go the route of variable with a nested sql statement therefore not using the @query within the send mail stored proc.

    Not sure what you meant by CDO? Maybe Collaboration Data Objects (CDO) using Report Server? However, I use database mail using SQL Server 2005.


    Thanks for the earlier replies. All is working great.

Posting Permissions

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