?
Solved

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

Posted on 2005-04-06
10
Medium Priority
?
446 Views
Last Modified: 2007-12-19
HI.

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


0
Comment
Question by:RivKin
8 Comments
 
LVL 8

Expert Comment

by:sigmacon
ID: 13718866
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
0
 

Author Comment

by:RivKin
ID: 13718965

The Trigger:

CREATE trigger T3 on dbo.MyTable
after insert
as

declare @mobid as int

Select @mobid =  mobid FROM inserted

EXEC CP_MainProce @mobid

****************************
The Procedure:

CREATE                       procedure CP_MainProce @mobrowid as int
as

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

                BEGIN.....
0
 
LVL 8

Accepted Solution

by:
sigmacon earned 2000 total points
ID: 13719132
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:RivKin
ID: 13719331
->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?
0
 
LVL 8

Expert Comment

by:sigmacon
ID: 13719606
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?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13720137
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
AS
SELECT dbo.udf_Main_Proce(mobid) FROM inserted
GO

CREATE FUNCTION CP_MainProce (@mobrowid int)
RETURNS int
AS
BEGIN

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
END
0
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13720178
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).
0
 

Author Comment

by:RivKin
ID: 14005574
thank you all.

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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 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