Calling Stored Procedure from a Trigger, Please Help!!!!

Posted on 2005-04-06
Last Modified: 2007-12-19

Using SQL2000

And going crazy here!!!

I’ve created an insert trigger on a table that calls a Stored Procedure.

The table is being updated from an external application.

After insertion, the trigger is caching the event and going in the SP yet inside the SP ther is a Select Query witch always returns Empty.

If I'm running the Stored Procedure in the query analyzer (with the same Vars) It works!!!

What Can It BE?

Please Help!!

Question by:RivKin
    LVL 8

    Expert Comment

    show the code ... both of the trigger and the stored proc. The stored proc may have parameters that your supply manually when testing outside the trigger, but you may not pass them correctly from inside the trigger

    Author Comment


    The Trigger:

    CREATE trigger T3 on dbo.MyTable
    after insert

    declare @mobid as int

    Select @mobid =  mobid FROM inserted

    EXEC CP_MainProce @mobid

    The Procedure:

    CREATE                       procedure CP_MainProce @mobrowid as int

    declare @Nrcsid as nvarchar(250)
    declare @CopyInTime as datetime

    set @CI = 24 /* main catalog id */

          SELECT     @Nrcsid = dbo.mobAttr.attrValue, @copyintime =  dbo.Mob.copyintime
          FROM         dbo.mobAttr INNER JOIN
                          dbo.Mob ON dbo.mobAttr.mobRowid = dbo.Mob.mobrowid
          WHERE     (dbo.mobAttr.mobRowid = @mobrowid ) AND (dbo.mobAttr.attrKey = 'NrcsID')
          insert into table2 (id,text) select @mobrowid, @Nrcsid    /*  THIS IS JEST FOR CHEKING THE VALUE THE RETERNS */

          while NOT (@Nrcsid IS null)    /* IN RUN TIME THE VALUE IS NULL IN MANUAL ITS OK */

    LVL 8

    Accepted Solution

    Is the value of @mobrowid that gets inserted into table2 for test purposes in your stored procedure what you would expect when calling it through the insert trigger? What I mean is that whether id you sent to the stored procedure when the trigger is 'triggered', does it exist in dbo.mobAttr.mobRowid? You would know that, because you are running the insert statement. Can you also print the insert statement with which you are testing and the manual call to the stored procedure? Thanks. Also is there another trigger that may add rows to mobAttr when rows are inserted into MyTable?

    Author Comment

    ->does it exist in dbo.mobAttr.mobRowid?

    Yse it does, i can see it when i do the manual test.

    ->Can you also print the insert statement :

    I jest open the Procedure Via the Analyzer (right click->Open) and then placine the value i want to pass it.

    -> Also is there another trigger that may add rows to mobAttr when rows are inserted into MyTable?

    There might be..... I'm not sure How can i check if this is the problem?
    LVL 8

    Expert Comment

    I actually meant for you to please show here both statements with which you insert data into MyTable and with which you test the stored procedure manually. If you created MyTable, and you did not create any other triggers, then my latter assumption about multiple triggers is not the problem. You can check in enterprise manager what triggers are defined for a table. So when you run the insert, the correct @mobrowid gets put into your test table2, with @nrcsid being NULL?
    LVL 34

    Expert Comment

    by:Brian Crowe
    part of your issue may be that you are assuming the trigger is called on a single record.  If you do a bulk insert to the table then the trigger is called once for the entire recordset.  I would suggest converting your stored procedure to a user-defined-function so you can call it inline

    CREATE trigger T3 ON dbo.MyTable
    AFTER insert
    SELECT dbo.udf_Main_Proce(mobid) FROM inserted

    CREATE FUNCTION CP_MainProce (@mobrowid int)
    RETURNS int

    SET @CI = 24 /* main catalog id */      --where are you declaring this?
    INSERT INTO table2 (id, text)
         SELECT dbo.mobAttr.attrValue, dbo.Mob.copyintime
         FROM dbo.mobAttr
         INNER JOIN dbo.Mob ON dbo.mobAttr.mobRowid = dbo.Mob.mobrowid
         WHERE (dbo.mobAttr.mobRowid = @mobrowid ) AND (dbo.mobAttr.attrKey = 'NrcsID')

    RETURN @@ROWCOUNT  --don't really need to return anything in this case
    LVL 9

    Expert Comment

    what is mobid in MyTable? is it an autoincrement number?
    when you insert a row in MyTable, are you sure this inserted mobid exists in mobAttr.mobRowid and Mob.mobrowid?
    otherwise the SELECT in your stored proc won't work.
    also, print the value of @mobrowid in the stored proc before the SELECT statement to check whether it is being passed properly (while the trigger executes the stored proc).

    Author Comment

    thank you all.

    The Accepted Answer was the one to lead me to the solution.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now