March 29, 2010 at 2:19 am
hi all
i want to monitor a table for any changes (update, create , delete) and send an email when ever there is a change. in the email i would want some of the fields that were changed or created.
is a trigger the best option? and if so can anyone point me in the right direction as im pretty new to SQL programming?
March 29, 2010 at 2:55 am
If your requirement is to send out the mail for every insert/update/delete it may slow down your application performance if number of transactions are high.
If number of transactions to this table is low trigger is suitable option.
Are you talking about "create" also? I guess this is insert.
March 29, 2010 at 3:04 am
hi Vidya
i actually only want to email on a create and delete. It is a stock table so really it should not have that many creates or deletes.
i am trying the below example, but, see where the If statement > 1000, if i omit this will i get all entries?
CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS
DECLARE @price money
DECLARE @item varchar(50)
SET @price = (SELECT price FROM inserted)
SET @item = (SELECT item FROM inserted)
IF @price >= 1000
BEGIN
DECLARE @msg varchar(500)
SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'
END
GO
March 29, 2010 at 3:29 am
From the above code you would get either one mail or no mail at all. You will have to use a cursor or a While loop to loop through all the records.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 29, 2010 at 3:49 am
really?
my thinking was the trigger would only be activated upon a new entry being CREATED?
why would i want to loop through all the records when i only want the new record?
March 29, 2010 at 4:02 am
I meant all the records that are inserted. You can have a statement that inserts 10 records or deletes 10 records at one go. Even in that case you would either get one mail or no mail at all.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 29, 2010 at 4:08 am
This trigger is fine. It should send the mail for each insert statement. In SQL Server trigger is fired at statement level.
If number of rows afftected(in update or delete opertaion) or inserted (using insert into select statement) are more than 1 then looping is reqired.
March 29, 2010 at 4:23 am
vidya_pande (3/29/2010)
This trigger is fine. It should send the mail for each insert statement. In SQL Server trigger is fired at statement level.If number of rows afftected(in update or delete opertaion) or inserted (using insert into select statement) are more than 1 then looping is reqired.
Thats what i said. But we should always assume that If something can happen it will happen
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 29, 2010 at 4:25 am
oh sorry i understand now
should not be a problem as you can only create or delete a product one at a time!
how can i check if a mail account has already been configured on the sql instance as when i do an update on the table i get an error stating xp sendmail is disabled, but i know that people already getting emails based on other certain table events. And i dont want undo any configurations by setting it up again!
March 29, 2010 at 4:43 am
its ok, iv got it working !
thanks all for your help!
i must keep in mind what you say about multiple records tho!
March 29, 2010 at 4:50 am
sorry one more question!
in the trigger i am setting the email body as below
set @msg = 'New Item created with a Product Code' "+ @StockCode +" ' and a description' "+ @Description +'''.'
but this does not work? stating error at @stockcode
how do i define fields that i am passing into the message?
March 29, 2010 at 5:10 am
looks like it's just a syntax error because of some double quotes; the double quotesd belong inside the single quotes to create the string.
assuming you wanted double quotes around the description, this should work:
set @msg = 'New Item created with a Product Code of "' + @StockCode + '" and a description "' + @Description +'".'
Lowell
March 29, 2010 at 5:15 am
cheers, thats it!
only thing now, if i have the fields defined as eg varchar(10)
i get
New Item created with a Product Code"PT123 "and a description"testpart ".
where i am getting spaces to fill the length!
can i get rid of that?
March 29, 2010 at 5:21 am
well, somehwere in your code, you have these two variables declared; just change their definition to a bigger value:(and make sure @msg is HUGE to hold the concat of these two)
now if the TABLE column definition is small, you'll need to fix that seperately.
ie
DECLARE @msg VARCHAR(8000)
DECLARE @StockCode VARCHAR (200)
DECLARE @Description VARCHAR(1000)
Lowell
March 29, 2010 at 5:28 am
sorry misunderstanding i think
i am getting
New Item created with a Product Code"PT123 "and a description"testpart ".
where the field placeholders are giving me spaces due to the varchar size
but i guess i just need to lpad these?
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply