Results 1 to 14 of 14

Thread: Calling SP

  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Calling SP

    Hi,
    Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.




    Thnkx,
    Rahul Jha

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rahul, have you TRIED this yourself?
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    I tried doing it georgy, but din get any error or warning message........ infact it executed successfully. I gt confused when i saw few blogs on the same saying that an stored procedure can't be called from the trigger........ hence thought of to put across to the trusted people on the forum.



    thnkx,
    rahul jha
    Last edited by DBA_Rahul; 09-17-07 at 06:05.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fancy posting the code you tried?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    This may be the wrong thread to ask this question on, but how does one go about requesting a userid change on this website?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As far as I know, it can be done (never tried it myself), but it tends to be a bad idea for performance reasons. You want the trigger to be as fast as possible, and not call any intricate processing that can take even tenths of seconds. Think of it this way, If you have a trigger that takes 0.1 seconds to run, it sounds good....until you try to insert 100 records (a very modest amount).

  7. #7
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Here is the code that is calling the trigger.

    CREATE PROCEDURE [dbo].[insert_tblCustomer_EDDCategory]
    AS
    BEGIN
    INSERT INTO tblCustomer_EDDCategory VALUES (1, 2, 'Casino and Bingo Operations', 7, getdate(), NULL, '1.00')
    END

    and this the code that executes the sp from trigger

    EXEC uspFetchAO
    Last edited by DBA_Rahul; 09-17-07 at 02:25.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And what does "uspFetchAO" do?
    George
    Home | Blog

  9. #9
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    there is just a select statement

  10. #10
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    and even if there is a INSERT statement in the called SP, it works. i.e. the record gets inserted into the table.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And finally...
    The code for the TRIGGER please.

    EDIT: Your trigger just runs a select statement?!
    what on earth?!
    George
    Home | Blog

  12. #12
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    You did not get my point Georgy. Let me write it in a step wise manner...

    1. I have a Stored Proc SP1 that INSERT a recod in table T1.
    2. Table T1 has a Trigger (After Insert), say TR1
    3. Trigger TR1 INSERT record in table T2
    4. Trigger TR1 has a statement that EXECUTE a Stored Proc SP2
    5. Stored Proc SP2 INSERT a record in table T3 and has a SELECT statement as well.
    6. Now when I EXECUTE SP1, there is no error message and all the tables (T1, T2 & T3) has new records.


    i.e. Stored Proc can be called from a Trigger?


    My experiment say 'Yes' Georgy. But the blogs on the net and one of the thread in dbforum say 'NO'. And here i got confused........ and thus knocked the door of the FORUM.........

    OVER......


    Thanks,
    Rahul Jha

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If the question is
    "Can you call a sproc from a trigger" then the answer is yes.
    Proof:
    Code:
    CREATE TABLE gvTest (
        Field0 int IDENTITY(1,1)
      , Field1 char(4)
      , Field2 char(4)
      , FieldN char(4)
      )
    GO
    
    CREATE TRIGGER gvTrig
    ON gvTest
    FOR INSERT
    AS
    SELECT 'Look, the INSERT trigger function worked!'
    GO
    
    CREATE PROCEDURE gvProc
    AS
    SELECT 'Look, the procedure worked!'
    GO
    
    CREATE TRIGGER gvTrigProc
    ON gvTest
    FOR UPDATE
    AS
    EXEC gvProc
    GO
    
    INSERT INTO gvTest(Field1) VALUES ('xxxx')
    
    UPDATE gvTest
    SET    Field2 = 'yyyy'
    
    DROP PROCEDURE gvProc
    DROP TRIGGER gvTrigProc
    DROP TRIGGER gvTrig
    DROP TABLE gvTest
    If the question is
    "Should I call sprocs from triggers" then the answer is further up this page.
    George
    Home | Blog

  14. #14
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Georgy, This was my question..............


    Hi,
    Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.




    Thnkx,
    Rahul Jha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •