April 13, 2004 at 1:42 pm
I am new to triggers, so here goes. On insert, I need the database to email the last inserted row to an exchange account and the person who submitted the information to the database. Here's what I have so far:
>CREATE TRIGGER last_insert ON [dbo].[empRequests]
>FOR INSERT
>AS
>declare @requestor varchar (25)
>declare sub_requestor cursor for
> select reqName
> from empRequests
>open sub_requestor
>fetch sub_requestor into @requestor
>deallocate sub_requestor
>
>declare @sub varchar(50)
>declare sub_request cursor for
> select empType
> from empRequests
>open sub_request
>fetch sub_request into @sub
>deallocate sub_request
>
>declare @query varchar(255)
>declare last_request cursor for
> select *
> from empRequests
> where NOT NULL
>open last_request
>fetch last_request into @query
>deallocate last_request
>
>exec master.dbo.xp_sendmail
>@recipients = 'general email adderss' + @requestor,
>@query = @query,
>@subject = @sub
>
>GO
First, I get an error at the "open..." lines.
Second, I'm not sure I'm going to get the results I want.
Any ideas?
Thanks!
April 14, 2004 at 1:55 am
There is an implicit table for insert or update triggers which contains the records that were inserted.
You can simply do the following
declare @requestor varchar (25)
select @requestor = requestor from inserted
similarly you can use table deleted in a delete trigger
April 14, 2004 at 5:00 am
Hi,
Use the special "Inserted" and "Deleted" tables to find the data you want (as posted above), but NEVER use xp_sendmail in a trigger. Triggers need to be short and sweet, and xp_sendmail can be, and is, problematic, and will "tie-up" your transaction when it encounters a problem.
Use the trigger to post your data to an intermediate table that gets processed by another task either by a scheduled job or other processing "loop". You will have separated the relatively costly mail process from your data update transaction, and will be able to use other mail tools if xp_sendmail becomes too problematic.
Kevin
April 14, 2004 at 6:49 am
I have to agree with Kevin. Particularly because if xp_sendmail fails your whole transaction will be rolled back. Put changes in an audit table. Then you also have the ability to look back in history at changes and develop reports to show who is making changes.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply