calling sprocs within a trigger

Question

calling sprocs within a trigger

yogiberr

Hall of Fame

Points: 3679
More actions
January 28, 2005 at 3:39 am

#66872

morning
I have a trigger and I need to call a sproc within it.
I am getting an error.Yet when copy the sproc's T-SQL into the trigger, then no problem.
Is this by design?I'd have thought that it'd be better practice to include a simple sproc call within the trigger, rather than littering triggers with extra sql statements.
cheers,
yogi

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

It's hard to say, you don't say what error is, or what the trigger and sproc are supposed to do.

In general, there is nothing that says that you can't or shouldn't call a proc from a trigger - it depends. If the statements are required for the trigger to function, I wouldn't consider the code placed there then as 'litter'. Though, for maintenance and readability's sake it might be a good idea to encapsulate 'functions' in separate procs, but then again - It depends.

just my .02 though

/Kenneth

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

cheers Kenneth

I definitely believe that it's better to encapsulate whenever possible. I'll include the trigger code, to see if that helps to clarify

<sql>

CREATE TRIGGER [trigUserProductPermissionDelete] ON dbo.tblUserProductPermission

FOR DELETE

AS

spReadFromTblUserProductPermission

</sql>

<error>

error 170: Incorrect syntax near 'spReadFromTblUserProductPermission'

</error>

yet, if I paste the code in-line, then no problem.

I also tried to prefix the name of the sproc with "dbo." but no joy.

I can confirm that:

1) the sproc exists within this database

2) I can run the sproc no problem thru QA

3) the sproc requires no parameters

I think that I am not CALLING the sproc correctly.

I tried to put a "RUN" a "DO" and a "CALL" before the sproc, as a guess, but, hee haw.

Any ideas?

cheers,

yogi

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

Did you try putting EXEC in front of proc name?

Good Hunting!

AJ Ahrens

webmaster@kritter.net

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

he he , I did now

..everything but the kitchen sink...

I'm a bit rusty.

thanks bud,

yogi