August 30, 2010 at 7:45 am
I want to be notified by mail when a specific value is inserted into a column in a table.
For example when a row with number 1 in the "number" column is inserted to a table or a row is updated to number 1 in the "number" column.
August 30, 2010 at 7:53 am
and for you request, look at Example B in the link i provided...
Excerpts from MSDN link
B. Using a DML trigger with a reminder e-mail message
The following example sends an e-mail message to a specified person (MaryM) when the Customer table changes.
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO
August 31, 2010 at 1:29 am
Thank you Ten.
It works fine.
I´m not a good programmer, so how will I do if I just want a message when, for example, a number 1 is inserted in a spesific column?
August 31, 2010 at 2:50 am
Thre will be 2 invisible magic tables inside a trigger, INSERTED and DELETED..
if a value is inserted into the table where the trigger is written upon, a row will be inserted into the INSERTED table.. You can query the INSERTED like your normal tables..
SELECT * FROM INSERTED
IF a value is updated, then an entry with the new set of values will be present in INSERTED and the old row whose values are updated will be inserted into the DELETED table. Again, use DELETED table like INSERTED table..
SELECT * FROM INSERTED
SELECT * FROM DELETED
Please read through the link i posted.. there will lot of examples that shows the operations that TRIGGERs do..
Hope this helps!
August 31, 2010 at 5:18 am
Hi Erik
I know you are not blocked any more with request but can you please elaborate on why you want to do this?
Can you also please answer the following
- What is the type of the table in question ? ( is it a transaction or Master table)
What is the role of this table from a application perspective?
-What is the frequency of this table been inserted or updated with data?
o If the table is frequently inserted or updated; a trigger should not be the way forward.
o It’s better to have a asynchronous approach in identifying the inserted and the updated data
? The transactions will be slower with a trigger implementation
-How soon do you want to be notified of the insert or update( of the specific values) ?
o If the table is frequently inserted or updated; you will get plenty of emails notifying you of the value change
August 31, 2010 at 6:13 am
Our system print errorcods in a table and I want a mail when we get this errorcods in the database
It is a user database and a user table holding errorcods
We hope that the frequency of this table been inserted or updated with data is not so often
-How soon do you want to be notified of the insert or update.
Answer As soon as possible.
This is what I have to work with I'am only the DBA and our developers want do be notified when the errorcods comming up in the database.
August 31, 2010 at 9:30 am
Thanks for the extra information
I’m not a great fan of triggers 🙂 , I’d suggest to schedule a job and send the email as part of the job.
There will be more work on implementing this approach and is somewhat asynchronous. And I strongly believe sending emails as part of a transaction is not beneficial( with the trigger approach).
Hope you have identity column on this table ( it will be needed if you plan to proceed with this approach)
August 31, 2010 at 9:37 am
I'd also recommend that you use a job and check periodically for these values. If there is an error, you could easily get overwhelmed with mail messages.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 1, 2010 at 7:20 am
Thank you all for your replays I have now solved it on both ways
Trigger way
CREATE TRIGGER [dbo].[reminder2]
ON [dbo].[my_table]
AFTER INSERT, UPDATE
AS
IF (SELECT COUNT(*) FROM inserted WHERE my_column = 'XX') = 1
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'My mail Mail Profile',
@recipients = 'My mail@',
@body = 'Database updated',
@subject = 'Database uppdated';
The SQL job way
IF (SELECT count (*) FROM my_table WHERE my_column = 'XX') > 1
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'My Mail Profile',
@recipients = 'My mail@',
@query = 'SELECT * FROM my_table
WHERE my_column = ''XX''' ,
@subject = 'Database updated',
@attach_query_result_as_file = 1 ;
September 1, 2010 at 9:31 am
Erik,
Thanks for the feedback.
IF (SELECT COUNT(*) FROM inserted WHERE my_column = 'XX') = 1
You could have > 1 row in the inserted table. It might be better to change this to:
IF EXISTS (SELECT 1 FROM inserted WHERE my_column = 'XX)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply