Post reply

Problem while executing SP from Trigger

  • March 21, 2014 at 6:30 am

    #289620

    Hi Members,

    Belwo is my requirement..

    Create trigger on Table_XYZ ( After Insert,After Update)

    select @var=Col_Last field is null from inserted

    if @var is null

    Execute sProc1

    create proc sproc1

    Business logic will create two csv files. Success and error files

    success record will be deleted from Table_XYZ,

    Error records updated with error_message.

    Problem:-

    When i am updating Col_Last value to null or insert a record with NULL for Col_Last

    Trigger is firing and executing SP.....But i was not able query the Table_XYZ and taking too much time...

    sp_who2 shows blocked by...my SPID only

    when i am executing SP alone ...every thing works fine....

    when i am doing testing on trigger with Insert/Update... i am getting executinf query... no response...

    If i comment below ones in Trigger...working fine...

    --if @var is null

    --Execute sProc1

    Appreciate your help...

    Thanks,

    Sasidhar Pulivarthi

  • March 21, 2014 at 7:56 am

    #1699094

    the trigger is always in an implicit transaction.

    if your procedure is trying to update the same table the trigger is on, it will block itself, and can never complete.your proc can manhandle otehr tables, but not the trigger table.

    two things:

    1: your current trigger design will not work when multiple rows are updated. i guarantee, there will come a time when this bites you, so redesign it up from to handle all rows where Col_Last field is null

    2. move the logic from the procedure into the trigger, or else call the procedure on a scheduled task for each row where Col_Last field is null, and delete the trigger altogether.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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