Calling SP

Hi,
Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.
Thnkx,
Rahul JhaRahul, have you TRIED this yourself?|||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|||Fancy posting the code you tried?|||This may be the wrong thread to ask this question on, but how does one go about requesting a userid change on this website? ;)|||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).|||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|||And what does "uspFetchAO" do?|||there is just a select statement|||and even if there is a INSERT statement in the called SP, it works. i.e. the record gets inserted into the table.|||And finally...
The code for the TRIGGER please.

EDIT: Your trigger just runs a select statement?!
what on earth?!|||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|||If the question is
"Can you call a sproc from a trigger" then the answer is yes.
Proof:

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