Post reply

Trigger Trouble

  • February 13, 2004 at 9:34 am

    #107540

    I have a trigger and it fires properly whenever I update two fields within the table.  It does not fire however when I insert a new record into the table.  I have listed the trigger and the Insert statement below: 

    Am I missing something?

     

    ---------------------------------------------

    Here's the trigger:

    CREATE TRIGGER trig_GetCustomerInfo ON dbo.tblMembers

    FOR INSERT, UPDATE

    AS

    exec stProc_GetCustomerInfo

    ---------------------------------------------

     

     

    ---------------------------------------------

    Here's the insert statement:

    Insert into tblMembers (

    custid,

    firstname,

    middle,

    lastname,

    address1,

    address2,

    city,

    state,

    zip,

    pphone,

    sphone,

    email,

    ovid,

    boflag,

    new,

    course

    )

    VALUES

    ('zzzzzzzz9999',

    'Gary',

    'G',

    'Gourmet',

    '12345 main st',

    '98765 main st.',

    'Cranston',

    'RI',

    '02920',

    '1234567890',

    '5555555555',

    'user@127.0.0.1',

    'GAAA10121138',

    0,

    1,

    '0203-0245')

    ---------------------------------------------

    Thanks for any help,

    Scott

  • February 13, 2004 at 11:22 pm

    #494075

    Scott,

    Have you considered putting some kind of tracer in the beginning of the trigger, before the exec statement, just to make sure that the trigger is not firing?  You could insert a row into a log table, issue xp_logevent to put a message into the Windows Application Event Log.*

    Let us know if that helps, and if not, post the table definition also (scripted from SQL EM would be best) and I'll see if I can replicate the problem.  It would be good in that case to post the output from SELECT @@VERSION also.

    Good luck,

    Chris

     

    * The Windows Application Event Log is also know as the Windows Application Application Log, but only on exactly one Web site. 

  • February 14, 2004 at 12:36 pm

    #494095

    Thanks, I'll post the info on Tuesday.

    Scott

  • February 17, 2004 at 8:31 am

    #494374

    The trigger gets fired but it does not execute the stored procedure within the trigger. It only executes the stored procedure when there is an update on the table. Any ideas? Thanks, Scott

  • February 17, 2004 at 8:33 am

    #494375

    You may even want to break them out separately -- one for update and one for insert.

  • February 17, 2004 at 8:46 am

    #494378

    I separated the triggers and there is still no luck. Please help, Scott

  • February 17, 2004 at 8:55 am

    #494382

    Do stored procedures called by a trigger get syntax checked before execution? In the stored procedure that gets called there is a cursor that queries for the inserted records within the table... I'm thinking that maybe the records are not in the table yet because the trigger is being executed? I don't know, but all I know is that I made another stored procedure that prints a text message and that one was fired during the trigger and the print command at the beginning of the stored procedure with the cursor in it did not get fired.

  • February 17, 2004 at 10:47 am

    #494402

    Scott,

    Can you post DDL for ... hmm ... the stProc_GetCustomerInfo proc, plus all the tables involved (tables affected by both the insert and the proc)?  Or would that be gobs of code?

    Also, when you say that the trigger fires but does not execute the procedure - you might have already done this, but did you put tracers inside the procedure also to make sure it's not being executed?  If the trigger you have posted is indeed the full trigger definition, it doesn't seem likely that the procedure isn't executing at all, since the procedure call is the only thing in the trigger (except for the tracers you put in).  Is that really what you've found out?  That would be odd indeed.

    Chris

  • February 17, 2004 at 5:04 pm

    #494449

    A few dozen print statements later... It turned out that a where clause was incorrect, but at least I learned how the inserted and deleted tables work during a trigger. Thanks for all the help, Scott

  • February 17, 2004 at 11:14 pm

    #494464

    Very good - I'm glad you got it figured out!

    Chris

Viewing 10 posts - 1 through 9 (of 9 total)

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