• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • Last Modified:

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


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!!

1 Solution
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
RivKinAuthor Commented:

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 */

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?
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

RivKinAuthor Commented:
->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?
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?
Brian CroweDatabase AdministratorCommented:
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)

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
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).
RivKinAuthor Commented:
thank you all.

The Accepted Answer was the one to lead me to the solution.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now