Executing SP from Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Executing SP from Trigger

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-22 : 13:38:20

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

<NOTHING>

END

Any suggestions/inputs would help

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-22 : 13:40:17

Whats the purpose of procedure can you explain?Also your code is assuming that INSERTED will be having a single row which is not always the case.

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-09-22 : 13:42:44

Looks good -- I don't think you need to specify ELSE if nothing else happens. If the IF condition isn't met, the trigger will just end. I'm not sure if there are benefits to either method.

http://msdn.microsoft.com/en-us/library/ms189799.aspx

Remember that it will run after every action you specify (insert and update in your case), so if this is going to be a highly transactional table, performance could suffer.

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-22 : 13:50:38

Is the syntax ok while executing the SP and I will remove else statement

Visakh16 - it only inserts one in the table at each time and this is for demo to get around a bug in the functionality

Thanks

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-22 : 15:03:42

I getting the following error

Msg 156, Level 15, State 1, Procedure TRG_EMPLOYEE, Line 10
Incorrect syntax near the keyword 'PROCEDURE'.

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)

END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 15:16:20

You either have a syntax error in the called procedure (can you execute just the procedure outside the trigger?) or you have not posted all the code. The word "procedure" is not in the code you posted.

Be One with the Optimizer
TG

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2008-09-22 : 15:37:23

quote:
Originally posted by TG

You either have a syntax error in the called procedure (can you execute just the procedure outside the trigger?) or you have not posted all the code. The word "procedure" is not in the code you posted.

Be One with the Optimizer
TG

Sorry I was trying to troubleshoot it different times for different error message

here is what I get

Msg 102, Level 15, State 1, Procedure TRG_SESN_ITM_ENTITY, Line 10
Incorrect syntax near '@PARTYID'.

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)

END

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-09-22 : 15:40:59

Assuming P_CREATE_PERSON is a stored procedure, take the parenthisis out from around the input parameter.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources