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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-17 : 10:06:31
|
| Zac writes "Guys,Can you call a stored procedure with a trigger. What are some pros/conns? Do you have an example? Any help would be greatly appreciated.Zac Myers" |
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-05-17 : 10:11:35
|
| Yes you can call an sp from a trigger. Pros and cons depends on your sp. In general, you don't want to have intensive processes running in your triggers. Other then that, I sparingly use triggers only when it is necessary to capture table events.*************************Someone done told you wrong! |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-17 : 10:25:56
|
| I like triggers myself but warnings for you. If you declare a trigger for insert the runs and extremely long stored proc attached to it.. be catious of how many inserts are run on this table. if you have 2 inserts on the statement every 5 seconds, you'll start noticing a large chunk of your server being used here. 2nd, something to watch for.. In sql7.0 (I'm not sure about 2000) triggers are unable to call stored procs in other databases. Just a warning there (sucks if your running XP's that exist in the master db) although easy to get around. Create a storedproc that calls the XP_. As far as examples go, well.. without getting to far in depth... I'll use the email table idea.Create trigger on emails for insert asdeclare @email varchar(100), @msg varchar(5000), @subject varchar250)set @email = email from insertedset @msg = message from insertedset @subject = subject from insertedexec send_email (@subject =@subject, @msg=@msg, @email=@email )then the stored proc would look likecreate proc send_email (@email varchar(100), @msg varchar(5000), @subject varchar(250)) as@msg = @msg + char(13) + 'this email was sent to you by my sql mail system.'(format it how/if you want)xp_sendmail @recipients = @email, @message = @msg, @subject = @subjectnow everytime a email, message, and subject are inserted this will send an email off to that person. May want to use some checks for nulls in the sp.if anything else is needed.. send me an email, I'll be glad to provide more |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-05-18 : 09:54:23
|
Wouldnt it be better , if the discussion continues here only. lot of people learn here reading others posts .and therez always a chance that someone else will have a better idea then yours.quote:
if anything else is needed.. send me an email, I'll be glad to provide more
Coming to Triggers, AFAIK they have the highest precedence . so, always avoid using stored procedure's which hog performance . esp, the xp_cmdsheel and xp_sendmail kinda.-------------------------------------------------------------- |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-05-20 : 20:36:45
|
| Your trigger won't complete until the stored procedure does, thus all the warnings you're seeing.I rarely use triggers in my work at this time, so somebody with more experience, please jump in here on this one... If an error occurs in your stored procedure, will it cause the trigger and its triggering event to be rolled back? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-05-20 : 20:57:31
|
| Mark,As with all errors in a proc in SQL Server the answer is.."Who Knows"!It does depend on the severity, but it *should* rollback the insertDavidM"SQL-3 is an abomination.." |
|
|
|
|
|
|
|
|
Calling Stored Procs with Triggers