|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server 2000: How to inject a COLUMN VALUE into an EMAIL???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. 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? Show quote "Alan Mailer" <clarityas***@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. Show quote On Sun, 11 Dec 2005 07:45:39 +0200, "Uri Dimant" <u***@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" <clarityas***@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. -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Alan Mailer" <clarityas***@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" <u***@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" <clarityas***@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. >> > 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_kara***@hotmail.nomail.com> wrote: Show quote >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". -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Alan Mailer" <clarityas***@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_kara***@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. > 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. Jens,
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. Show quote On 14 Dec 2005 00:10:44 -0800, "Jens" <J***@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 = '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 = @RowId exec @RC = master.dbo.xp_smtp_sendmail @server = N'mail.sqldev.net', @from = N'SomeMailAdress', @to = N'SomeMailAdress', @subject = 'New record added.' , @Message = @Message --Delete the one thta has been send successfully IF @RC != 0 DELETE FROM SomeTable4EMail WHere RowId = @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´t be deleted and tried at the next time. DROp table SomeTable DROp table SomeTable4EMail /* HTH, Jens Suessmeyer.  |
|||||||||||||||||||||||
Other interesting topics