Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: Triggers Never Finish

    I am a VoIP phone system using SQL on the back end. I am trying to get a Trigger to fire and email me when a certain number has been dialed.

    Create Trigger trg_Emergency_Calls on dbo.CallDetailRecord for Insert

    IF @@ROWCOUNT=0 RETURN ---NO rows affected exit proc

    IF (SELECT finalcalledpartynumber FROM inserted)='95593684' BEGIN
    --RAISERROR ('Call Stored Procedure Here',16,10)
    EXEC WEB_SRVR03.master.dbo.sp_SMTPMail @body='This is a test Email'

    END
    Return

    GO

    The problem is I have to execute the actually email SP is on another server and has to be that way. The trigger actually runs each time but if the IF statement becomes true then the trigger hangs and never completes. Watching the other server(WEB_SRVR03) there is never a request to execute the sp_SMTPMail. I have been trying to troubleshoot this with profiler but I never see any locks or anything that would give me a problem. Also the insert statement that caused the trigger to fire also never finishes and so the record isn't written to the db. If anyone has any suggestions I would appreciate it.
    Thanks
    Last edited by GarrettD; 01-02-04 at 11:48.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    First, I think you should drop the IF @@ROWCOUNT line. I'm not sure you can have any confidence as to what process that @@ROWCOUN variable is counting, and if there were no inserted rows then not only would your second statement evaluate as false, but your trigger wouldn't even fire at all.

    Next, what happens if you run...
    ------------------------------------
    EXEC WEB_SRVR03.master.dbo.sp_SMTPMail @body='This is a test Email'
    ------------------------------------
    ...directly in QUERY ANALYZER?

    Also, to troubleshoot, create a dummy table like this:
    ------------------------------------
    CREATE TABLE [dbo].[TSHOOT] ([Message] [varchar] (500), [Occured] [datetime] NULL) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[TSHOOT] WITH NOCHECK ADD CONSTRAINT [DF_TSHOOT_Occured] DEFAULT (getdate()) FOR [Occured]
    GO
    ------------------------------------

    Then rewrite your trigger like this:
    ------------------------------------
    Create Trigger trg_Emergency_Calls on dbo.CallDetailRecord for Insert

    IF (SELECT finalcalledpartynumber FROM inserted)='95593684' BEGIN
    insert into TSHOOT (Message) SELECT finalcalledpartynumber FROM inserted

    END
    Return

    GO
    ------------------------------------

    blindman

  3. #3
    Join Date
    Jan 2004
    Posts
    7
    If I run the
    EXEC WEB_SRVR03.master.dbo.sp_SMTPMail @body='This is a test Email'

    in Query Analyer it executes fine and I end up recieving the email.
    I will try with the dummy table and reply soon.

  4. #4
    Join Date
    Jan 2004
    Posts
    7
    I have found that the insert into the dummy table using the trigger does complete. (Sorry, posted a while ago that it didn't, but that was a screwup on my part) So what does this mean as far as troubleshooting why that SP won't execute.
    G

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you only inserting one row at a time?

    Maybe it has something to do with whether the SQL Server service has permission to use the mail utility, vs your login's permission when you execute through query analyzer? Just guessing now, sorry...

    blindman

  6. #6
    Join Date
    Jan 2004
    Posts
    7
    Thanks Blindman
    I am actually not sure how all the rows are inserted, I think it maybe a batch job of some kind, maybe even a DTS package. But all of this is in code that I can't see. Would it work if I took the trigger and maybe attempted it on the TShoot table, so when the insert occurs on the TShoot there is a trigger there that attempts the email.

  7. #7
    Join Date
    Jan 2004
    Posts
    7
    Blindmand Thanks for the help. When you mentioned User I checked and the linked server permissions were linked together but the app used another user which was also way the new table was able to get the insert from the trigger but the email would never go out.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So it works now?

  9. #9
    Join Date
    Jan 2004
    Posts
    7
    Just like a charm.

Posting Permissions

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