September 22, 2008 at 11:38 am
I am trying to execute a stored procedure from a trigger, Is this the right way to do it interms of syntax and logic
create trigger [TRG_EMPLOYEE] ON EMPLOYEE AFTER INSERT, UPDATE AS
BEGIN
DECLARE @PARTYID BIGINT
DECLARE @PARTYTYP INT
SELECT @PARTY_ID = PARTY_ID, @PARTYTYP = PARTYTYP FROM INSERTED
IF @PARTYTYP = 1
EXEC P_CREATE_PERSON (@PARTYID)
ELSE
END
Any suggestions/inputs would help
Thanks
September 22, 2008 at 11:58 am
IMO this is not the way to do it. A trigger fires once per batch so the trigger you have defined will only call the stored procedure for one row and you are not guaranteed which row. The trigger also takes place WITHIN a transaction so if you have an error in your your stored procedure the entire batch will be rolled back. Now, it is possible that you want that behavior, but you have to be sure.
What are you trying to do with the trigger? Is there a way to accomplish the task using set-based logic so that the trigger can handle a batch situation? You can still use the stored procedure for that by using Select * Into #temp From Inserted and then calling your stored procedure which will have access to #temp. I'm not saying this is the best way to handle it, but without details it's hard to give a complete answer.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply