|
|
You're in the
sql server programming
group:SQL Server 2000: How to inject a COLUMN VALUE into an EMAIL???
sql server programming:
Using SQL Server 2000, I want to trigger an email anytime a particular
table is appended. I already know the SQL language which will trigger an email, but I need to know something more: Is there a way to add a value from one of the appended row's columns to the email? If so, how would you go about it? For what it's worth, my current INSERT Trigger looks something like this: CREATE TRIGGER [INSERT_MyTable] ON [dbo].[tblMyTable] FOR INSERT AS exec sproc_SendMeEmail 'FromAddress','ToAddress','MyEmailSubject','MyEmailBody' Say the value I wanted to inject into my email was located in the newly appended record's 'MyColumn' column. How would you inject this value into the body of the email which should be triggered whenever tblMyTable is appended? By the way, if it helps you to know this, 'sproc_SendMeEmail' looks something like this: CREATE PROCEDURE [sproc_SendMeEmail] (@From varchar(100), @To varchar(100), @Subject varchar (100), @Body varchar (4000) ) AS exec master.dbo sp_send_cdosysmail @From, @To, @Subject, @Body GO
Alan
Using /Sending Emails within a trigger is very bad idea Think, if your transaction is failed (INSERT in your case) but trigger will send an email to someone. Do you want someone to get a wrong information? [quoted text, click to view] "Alan Mailer" <clarityassoc@earthlink.net> wrote in message news:0bkmp152irlkeosadle3633p69oc948um1@4ax.com... > Using SQL Server 2000, I want to trigger an email anytime a particular > table is appended. I already know the SQL language which will trigger > an email, but I need to know something more: > > Is there a way to add a value from one of the appended row's columns > to the email? If so, how would you go about it? > > For what it's worth, my current INSERT Trigger looks something like > this: > > CREATE TRIGGER [INSERT_MyTable] ON [dbo].[tblMyTable] > FOR INSERT > AS > exec sproc_SendMeEmail > 'FromAddress','ToAddress','MyEmailSubject','MyEmailBody' > > Say the value I wanted to inject into my email was located in the > newly appended record's 'MyColumn' column. How would you inject this > value into the body of the email which should be triggered whenever > tblMyTable is appended? > > By the way, if it helps you to know this, 'sproc_SendMeEmail' looks > something like this: > > CREATE PROCEDURE [sproc_SendMeEmail] > (@From varchar(100), > @To varchar(100), > @Subject varchar (100), > @Body varchar (4000) > ) > AS > > exec master.dbo sp_send_cdosysmail @From, @To, @Subject, @Body > GO > > > ....Thanks in advance.
I really appreciate you advice re 'sending emails in triggers'. I
might make a change in where I choose to have SQL Server 'trigger' (forgive the pun) the email. Whether I do or not though, I'm still going to need to know if if an SQL Server 2000-generated email can contain a row's column value. I'm still hoping someone will address that specifically. Thanks again for your response. On Sun, 11 Dec 2005 07:45:39 +0200, "Uri Dimant" <urid@iscar.co.il> [quoted text, click to view] wrote:
>Alan > >Using /Sending Emails within a trigger is very bad idea > >Think, if your transaction is failed (INSERT in your case) but trigger will >send an email to someone. Do you want someone to get a wrong information? > > > >"Alan Mailer" <clarityassoc@earthlink.net> wrote in message >news:0bkmp152irlkeosadle3633p69oc948um1@4ax.com... >> Using SQL Server 2000, I want to trigger an email anytime a particular >> table is appended. I already know the SQL language which will trigger >> an email, but I need to know something more: >> >> Is there a way to add a value from one of the appended row's columns >> to the email? If so, how would you go about it? >> >> For what it's worth, my current INSERT Trigger looks something like >> this: >> >> CREATE TRIGGER [INSERT_MyTable] ON [dbo].[tblMyTable] >> FOR INSERT >> AS >> exec sproc_SendMeEmail >> 'FromAddress','ToAddress','MyEmailSubject','MyEmailBody' >> >> Say the value I wanted to inject into my email was located in the >> newly appended record's 'MyColumn' column. How would you inject this >> value into the body of the email which should be triggered whenever >> tblMyTable is appended? >> >> By the way, if it helps you to know this, 'sproc_SendMeEmail' looks >> something like this: >> >> CREATE PROCEDURE [sproc_SendMeEmail] >> (@From varchar(100), >> @To varchar(100), >> @Subject varchar (100), >> @Body varchar (4000) >> ) >> AS >> >> exec master.dbo sp_send_cdosysmail @From, @To, @Subject, @Body >> GO >> >> >> ....Thanks in advance. >
You get the modified data through the inserted and deleted tables. Just remember that these will
contain more then one row for multi-row modifications. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Alan Mailer" <clarityassoc@earthlink.net> wrote in message
news:f6oop1tea2b5p5dctk1b0pk0s6mcbqrmge@4ax.com... >I really appreciate you advice re 'sending emails in triggers'. I > might make a change in where I choose to have SQL Server 'trigger' > (forgive the pun) the email. > > Whether I do or not though, I'm still going to need to know if if an > SQL Server 2000-generated email can contain a row's column value. I'm > still hoping someone will address that specifically. > > Thanks again for your response. > > On Sun, 11 Dec 2005 07:45:39 +0200, "Uri Dimant" <urid@iscar.co.il> > wrote: > >>Alan >> >>Using /Sending Emails within a trigger is very bad idea >> >>Think, if your transaction is failed (INSERT in your case) but trigger will >>send an email to someone. Do you want someone to get a wrong information? >> >> >> >>"Alan Mailer" <clarityassoc@earthlink.net> wrote in message >>news:0bkmp152irlkeosadle3633p69oc948um1@4ax.com... >>> Using SQL Server 2000, I want to trigger an email anytime a particular >>> table is appended. I already know the SQL language which will trigger >>> an email, but I need to know something more: >>> >>> Is there a way to add a value from one of the appended row's columns >>> to the email? If so, how would you go about it? >>> >>> For what it's worth, my current INSERT Trigger looks something like >>> this: >>> >>> CREATE TRIGGER [INSERT_MyTable] ON [dbo].[tblMyTable] >>> FOR INSERT >>> AS >>> exec sproc_SendMeEmail >>> 'FromAddress','ToAddress','MyEmailSubject','MyEmailBody' >>> >>> Say the value I wanted to inject into my email was located in the >>> newly appended record's 'MyColumn' column. How would you inject this >>> value into the body of the email which should be triggered whenever >>> tblMyTable is appended? >>> >>> By the way, if it helps you to know this, 'sproc_SendMeEmail' looks >>> something like this: >>> >>> CREATE PROCEDURE [sproc_SendMeEmail] >>> (@From varchar(100), >>> @To varchar(100), >>> @Subject varchar (100), >>> @Body varchar (4000) >>> ) >>> AS >>> >>> exec master.dbo sp_send_cdosysmail @From, @To, @Subject, @Body >>> GO >>> >>> >>> ....Thanks in advance. >> >
First read this, as stated before don=B4t use this directly in a trigger ! http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/= thread/f99983360fe14022 CREATE TABLE SomeTable ( SomeColumn VARCHAR(200) ) CREATE TRIGGER TRG_SomeTable On SomeTable FOR INSERT AS BEGIN DECLARE @ROWS INT DECLARE @I INT DECLARE @Subject VARCHAR(200) IF @@ROWCOUNT > 0 SELECT *, IDENTITY(INT,1,1) AS Counter INTO #TempTable FROM INSERTED SEt @ROWS =3D @@ROWCOUNT WHILE @I <=3D @ROWS BEGIN SELECT @Subject =3D SomeColumn + ' inserted into table.' from #TempTable Where Counter =3D @I exec master.dbo.xp_smtp_sendmail @server =3D N'mail.sqldev.net', @from =3D N'', @to =3D N'', @subject =3D @Subject SET @I =3D @I + 1 END END HTH, Jens Suessmeyer.
Tibor, thanks for responding. Could you give me an example of Trigger
language that retrieves the value of a column from a newly inserted row? It is really the specific trigger language that could do this that I'm looking for. Thanks again to everyone who has been, and is, willing to help. On Sun, 11 Dec 2005 21:27:44 +0100, "Tibor Karaszi" [quoted text, click to view] <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote:
>You get the modified data through the inserted and deleted tables. Just remember that these will >contain more then one row for multi-row modifications.
Be aware that a trigger doesn't fire one time for each row modified, it fires once per statement.
There are examples for triggers in Books Online. Be careful to read the section "mutirow considerations". -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ [quoted text, click to view] "Alan Mailer" <clarityassoc@earthlink.net> wrote in message news:im6vp1t4mar2c18f7uqo838jmfpm00l5f3@4ax.com... > Tibor, thanks for responding. Could you give me an example of Trigger > language that retrieves the value of a column from a newly inserted > row? > > It is really the specific trigger language that could do this that I'm > looking for. > > Thanks again to everyone who has been, and is, willing to help. > > On Sun, 11 Dec 2005 21:27:44 +0100, "Tibor Karaszi" > <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote: > >>You get the modified data through the inserted and deleted tables. Just remember that these will >>contain more then one row for multi-row modifications. >
Jens,
[quoted text, click to view] Thanks so much for your response. I read both the message you wrote in the link you provided and your suggested Trigger language.I hope I'm interpreting correctly the advice you were giving in that August message. From that message it sounds like I should be able to create a SQL Server 2002 procedure that will regularly check a table to see which rows have been added since a certain date. I don't want to press my luck here, but... If I'm right about how I've read your advice, it would help to know how to write a SQL Server 2002 procedure that would do what you're saying; a procedure that would gather a particular column's values into a string that would eventually be sent in an email. To be clear, I think I know how to generate an email in SQL Server 2002. What I don't know how to do is specifically what I've mentioned in the previous paragraph. If you, or anyone reading this, can help further, thank you. Whether or not you can help further, I'm very grateful for the information you have provided so far. Thanks again. [quoted text, click to view] On 14 Dec 2005 00:10:44 -0800, "Jens" <Jens@sqlserver2005.de> wrote:
> >First read this, as stated before don´t use this directly in a trigger >! > >http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/f99983360fe14022 > > > >CREATE TABLE SomeTable >( > SomeColumn VARCHAR(200) >) > >CREATE TRIGGER TRG_SomeTable On SomeTable >FOR INSERT >AS >BEGIN > >DECLARE @ROWS INT >DECLARE @I INT >DECLARE @Subject VARCHAR(200) > > >IF @@ROWCOUNT > 0 > >SELECT *, IDENTITY(INT,1,1) AS Counter INTO #TempTable >FROM INSERTED > >SEt @ROWS = @@ROWCOUNT > >WHILE @I <= @ROWS >BEGIN > SELECT @Subject = SomeColumn + ' inserted into table.' from >#TempTable Where Counter = @I > exec master.dbo.xp_smtp_sendmail @server = N'mail.sqldev.net', @from >= N'', @to = N'', @subject = @Subject > SET @I = @I + 1 >END > > >END > > >HTH, Jens Suessmeyer.
Building on top of my previous post: CREATE TABLE SomeTable ( SomeColumn VARCHAR(200) ) GO CREATE TABLE SomeTable4EMail ( RowId INT IDENTITY(1,1), SomeColumn VARCHAR(200) , AddedAt DATETIME DEFAULT GETDATE(), AddedFrom VARCHAR(200) DEFAULT HOST_NAME(), Addedby VARCHAR(200) DEFAULT SYSTEM_USER ) CREATE TRIGGER TRG_SomeTable On SomeTable FOR INSERT AS BEGIN INSERT INTO SomeTable4EMail ( SomeColumn ) SELECT SomeColumn FROM INSERTED END GO CREATE PROCEDURE P_SendInsertedRows AS BEGIN DECLARE @RowId INT DECLARE @RC INT DECLARE @Message VARCHAR(4000) DECLARE CRSSendMail CURSOR READ_ONLY FOR SELECT RowId from SomeTable4Email OPEN CRSSendMail FETCH NEXT FROM CRSSendMail INTO @RowId WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN SELECT @message =3D 'The following row with the value ' + SomeColumn + CONVERT(VARCHAR(100),AddedAt) + ' by the user ' + CONVERT(VARCHAR(100),AddedBy) + ' from the host ' + CONVERT(VARCHAR(100),AddedFrom) FROM SomeTable4EMail Where RowId =3D @RowId exec @RC =3D master.dbo.xp_smtp_sendmail @server =3D N'mail.sqldev.net', @from =3D N'SomeMailAdress', @to =3D N'SomeMailAdress', @subject =3D 'New record added.' , @Message =3D @Message --Delete the one thta has been send successfully IF @RC !=3D 0 DELETE FROM SomeTable4EMail WHere RowId =3D @RowId END FETCH NEXT FROM CRSSendMail INTO @RowId END CLOSE CRSSendMail DEALLOCATE CRSSendMail END */ On top of that you have to build a job with a TSQL step which executes the job on a regular basis (like every minute or the period you want to). It will send the emails out with the appropiate row information, If an error occurs the row won=B4t be deleted and tried at the next time. DROp table SomeTable DROp table SomeTable4EMail /* HTH, Jens Suessmeyer.
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |