Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
zxswordxz
Starting Member
4 Posts |
 Posted - 2011-05-23 : 11:47:12
|
| I'm currently running an apps on our SQL 2000 Ent server. I have a trigger setup to notify user when a new record has been inserted/updated via email. However, I want to change this procedure because it sends out an email every time there is any change and I was thinking of setting up an SQLJob with a TSQL script to send one email at the end of the day listing all the rows that was inserted/updated. This is my currently trigger. Can this be done? Current Trigger:CREATE TRIGGER [New PO] ON [dbo].[_ORDER_HEADER] FOR INSERT, UPDATEASDeclare @bulk_po as varchar(10)Declare @ship_start as varchar(20)Declare @division as varchar(10)Declare @release_po as varchar(10)Set @bulk_po = (Select bulk_po from inserted)Set @ship_start = (Select ship_start from inserted)Set @division = (Select division from inserted)Set @release_po = (Select release_po from inserted)Declare @Msg as Varchar(500)SET @Msg='Visit http://blahblah.com/ Report #41 for more details.'+ char(13) + char(10) + 'Bulk PO Number:' +Left(@bulk_po,6) +char(13) + char(10) + 'Release Number:' + @release_po +char(13) + char(10) + 'Division:' + @division +char(13) + char(10) + 'NDC:' + Convert(varchar,convert(datetime, @Ship_start),101)Declare @Subj as Varchar(500)Set @Subj = 'New/Revised PO'exec master..xp_startmailexec master..xp_sendmail @recipients='user_email@dot.com',@message=@Msg, @copy_recipients='myself@myself.com',@subject=@Subjexec master..xp_stopmailAppreciate any help I can get. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zxswordxz
Starting Member
4 Posts |
Posted - 2011-05-23 : 16:01:52
|
| Is it possible to create a temp table and insert certain column into the temp table with a trigger. Then use TSQL to query the temp table, email the query and then drop the temp table? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
zxswordxz
Starting Member
4 Posts |
Posted - 2011-05-23 : 17:28:58
|
| Thanks tkizer....I've created the permanent table, create the trigger to update it. It work great. This is what I have for the TSQL but it doesn't allow me to format the data on the email so it look more like a report. Is there a way to format the data and add my own header?exec master..xp_startmailexec master..xp_sendmail @recipients = 'myself@company.com', @subject = 'New/Revised EDI PO', @query = 'SELECT Left(Bulk_PO,6),Release_Po, Division, Ship_start,Ship_cancel, Ship Cancel from _order_header_temp', @message = 'Attached is a list of PO that has been Rececived', @attach_results = FALSE,@dbuse = 'LTL'exec master..xp_stopmail |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Email Inserted/Updated Records