March 1, 2016 at 12:01 pm
Hi All,
I work for a community center and we offer many classes/programs to community.
I need to be able to send alerts\emails to different depts about online registrations.
Whenever someone register for a class\program, I want to get an alert as well as to the respective dept which offer those programs\classes.
I was thinking to create a store procedure or trigger and run it as a SQL Server Agent job every hour.
I am not very good at this, so need your help/guidance to get this accomplished.
Also, looking to see if there are any free third party tools out there which can accomplish the same.
I believe all the registrations are stored in activity table, so I am attaching the create script for the dbo.activity table.
There is a column called activity_type = meeting and product_code column for different programs/courses.
let me know your thoughts and let me know if my requirements are clear.
Regards,
SQLisAwe5oMe.
March 1, 2016 at 12:30 pm
What version of sql server are you using? You have a lot of deprecated things going on in there. You have what appears to be a lack of normalization with notes and UF columns. You have the text datatype which has been deprecated since 2005 in favor of (n)varchar(max). You have the image datatype which has been deprecated since 2005 in favor of varbinary(max). You have a timestamp column which has also been deprecated and probably isn't really needed here. And varchar(1) for a status is rather funny. Why not simply char(1)?
At any rate, what is the typical best approach to this type of situation is to create a holding table for email/alerts to be sent out. You can use a trigger to populate the holding table. You do NOT want to send out these notifications from your trigger because you don't want to delay data being inserted while an email is being created and sent. Instead you create a sql job that pulls the data from the holding table on a set interval and sends out notifications.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 1, 2016 at 12:41 pm
Sean Lange (3/1/2016)
What version of sql server are you using? You have a lot of deprecated things going on in there. You have what appears to be a lack of normalization with notes and UF columns. You have the text datatype which has been deprecated since 2005 in favor of (n)varchar(max). You have the image datatype which has been deprecated since 2005 in favor of varbinary(max). You have a timestamp column which has also been deprecated and probably isn't really needed here. And varchar(1) for a status is rather funny. Why not simply char(1)?At any rate, what is the typical best approach to this type of situation is to create a holding table for email/alerts to be sent out. You can use a trigger to populate the holding table. You do NOT want to send out these notifications from your trigger because you don't want to delay data being inserted while an email is being created and sent. Instead you create a sql job that pulls the data from the holding table on a set interval and sends out notifications.
Hi Sean, thanks for the quick reply.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Standard Edition
I am very new to this env, so still studying the env.
Anyway, this is a third party app, so I don't have much control over the development.
I am not sure what exactly you meant by holding table? is it #table? Do you have a sample SP which works similarly?
Regards,
SQLisAwe5oMe.
March 1, 2016 at 12:48 pm
This is my breakdown of requirement.
-Check if any changes to ACTIVITY table
-Activity_Type = 'MEETING'
-Product_Code = different product codes for different programs/classes.
-Find out the Product_Code for which had activity
-First 2 characters of the Product_Code defines which dept that program/class belongs to...
-Join dbo.Name table to get the customer details
-Send out email with the changes
Regards,
SQLisAwe5oMe.
March 1, 2016 at 1:07 pm
SQLisAwE5OmE (3/1/2016)
This is my breakdown of requirement.-Check if any changes to ACTIVITY table
-Activity_Type = 'MEETING'
-Product_Code = different product codes for different programs/classes.
-Find out the Product_Code for which had activity
-First 2 characters of the Product_Code defines which dept that program/class belongs to...
-Join dbo.Name table to get the customer details
-Send out email with the changes
Yeah the first part of this would be in an update trigger. This is where you determine what changes indicate a need to send out a notification. You would insert some details about the notification into another table. This would contain whatever details you need to send out. You need to define and create this table as it will be specific to your requirements.
Then you would create a sql job that will come along and read the contents of that table, create the email and send it out. Does this make more sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 1, 2016 at 1:12 pm
Sean Lange (3/1/2016)
SQLisAwE5OmE (3/1/2016)
This is my breakdown of requirement.-Check if any changes to ACTIVITY table
-Activity_Type = 'MEETING'
-Product_Code = different product codes for different programs/classes.
-Find out the Product_Code for which had activity
-First 2 characters of the Product_Code defines which dept that program/class belongs to...
-Join dbo.Name table to get the customer details
-Send out email with the changes
Yeah the first part of this would be in an update trigger. This is where you determine what changes indicate a need to send out a notification. You would insert some details about the notification into another table. This would contain whatever details you need to send out. You need to define and create this table as it will be specific to your requirements.
Then you would create a sql job that will come along and read the contents of that table, create the email and send it out. Does this make more sense?
I understood about creating job/setting up alerts/notifications part.....but trigger coding, not very good at it.
Can someone plz provide the code based on my requirement?....is that too much to ask?....:)
Regards,
SQLisAwe5oMe.
March 1, 2016 at 1:27 pm
SQLisAwE5OmE (3/1/2016)
Sean Lange (3/1/2016)
SQLisAwE5OmE (3/1/2016)
This is my breakdown of requirement.-Check if any changes to ACTIVITY table
-Activity_Type = 'MEETING'
-Product_Code = different product codes for different programs/classes.
-Find out the Product_Code for which had activity
-First 2 characters of the Product_Code defines which dept that program/class belongs to...
-Join dbo.Name table to get the customer details
-Send out email with the changes
Yeah the first part of this would be in an update trigger. This is where you determine what changes indicate a need to send out a notification. You would insert some details about the notification into another table. This would contain whatever details you need to send out. You need to define and create this table as it will be specific to your requirements.
Then you would create a sql job that will come along and read the contents of that table, create the email and send it out. Does this make more sense?
I understood about creating job/setting up alerts/notifications part.....but trigger coding, not very good at it.
Can someone plz provide the code based on my requirement?....is that too much to ask?....:)
Actually yes it is too much to ask since you haven't provided us any real details. We have no tables to work with, no real business rules, no explanation about what kinds of details you want to send in this email....
At this point all we know is that you want to send an email based on some very loose requirements and you expect somebody to write the code for you but you haven't even provided the ddl for the tables. Here is a better idea. How about if you try this by yourself and post back what you try? Then we can help you finish your solution.
This link should help you get started. https://msdn.microsoft.com/en-us/library/ms189799.aspx Remember that you do not want to use scalar variables in your trigger. Any query needs to be set based because they fire once per operation, not once per row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 1, 2016 at 1:32 pm
Okay, thanks Sean.
I will try and see.
Regards,
SQLisAwe5oMe.
March 1, 2016 at 1:57 pm
If this is a third party app, writing a trigger on the table will almost certainly invalidate any maintenance agreement that you have with them.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 1, 2016 at 4:24 pm
drew.allen (3/1/2016)
If this is a third party app, writing a trigger on the table will almost certainly invalidate any maintenance agreement that you have with them.Drew
Hmm....don't really agree....I'm not touching the app or their design....this is more of an audit stuff, and if I ask for their assistance, I'm sure it will be expensive.
Regards,
SQLisAwe5oMe.
March 1, 2016 at 5:41 pm
Anyone heard of a tool called TaskCentre?
Regards,
SQLisAwe5oMe.
March 2, 2016 at 8:15 am
SQLisAwE5OmE (3/1/2016)
drew.allen (3/1/2016)
If this is a third party app, writing a trigger on the table will almost certainly invalidate any maintenance agreement that you have with them.Drew
Hmm....don't really agree....I'm not touching the app or their design....this is more of an audit stuff, and if I ask for their assistance, I'm sure it will be expensive.
Aren't you? Aren't the tables included in the design? Isn't adding a trigger to a table touching that table and therefore the design? And what if your trigger causes an error that prevents an insert/update to that table? Doesn't that affect their app?
And are you so sure of your opinion that you believe that it will stand up in court? Are you willing to stake your job on it? I'm not, so I would err on the side of caution.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 7, 2016 at 9:48 am
Hi Guys,
Please help.
I was trying to create the Insert Trigger, below is the code.
I did not write the code from scratch, found a DML trigger, from this post....
http://qa.sqlservercentral.com/Forums/Topic1441032-1550-3.aspx
I am getting the below error, when trying to create the trigger.
Msg 207, Level 16, State 1, Procedure Orders_Online_Reg, Line 25
Invalid column name 'Name'.
Msg 207, Level 16, State 1, Procedure Orders_Online_Reg, Line 25
Invalid column name 'Version'.
Msg 207, Level 16, State 1, Procedure Orders_Online_Reg, Line 26
Invalid column name 'Timestamp'.
/* ================================================
* Need to send alert, whenever an online registrations occur.
* Once order created for an online registration, send an email to a distribution list.
* Tables involved
* dbo.Orders(Columns needed)- Order_Number, Order_Date, ST_ID, Full_Name, SOURCE_CODE
* dbo.Order_Meet(Columns Needed) - Order_Number, Meeting
=================================================*/
--/****** Object: Table [dbo].[logger]*****/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[logger_all](
[Body] [varchar](500) NULL,
[ORDER_NUMBER] varchar(100),
[Subject] [varchar](104) NULL,
[Name] [varchar](200) NULL,
[Version] [varchar](100) NULL,
[Timestamp] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Version varchar(100)
Declare @Timestamp varchar(100)
declare @optype tinyint = 0;
if exists (select * from inserted) set @optype = @optype+1
if exists (select * from deleted) set @optype = @optype+2
BEGIN
set @Subject =
case @optype
when 1 then 'New row inserted into Orders table in XXXXXX'
else 'Nothing Happened'
end ;
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Name = i.Name, @Version = .[Version],
@Timestamp = i.[Timestamp]
from inserted i
if @optype = 1 or @optype = 3
select @Body = 'New record has been Updated in Orders table in XXXXXX, Following are the details ' + char(13)
+ 'ORDER_NUMBER : ' + ISNULL(@ORDER_NUMBER,'[Missing ConfigSet]') + CHAR(13)
+ 'Name : ' + ISNULL(@Name ,'[Missing Name]') + CHAR(13)
+ 'Version : ' + ISNULL(@Version,'[Missing Version]') + CHAR(13)
+ 'TimeStamp : ' + ISNULL(@Timestamp,'[Missing Timestamp]') + CHAR(13)
from inserted i ;
INSERT INTO dbo.logger_all SELECT @Body, @ORDER_NUMBER, @Subject, @Name, @Version, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'Default Mail Account',
@recipients = '@somedomain.com',
@subject = @subject,
@body = @body
END
Regards,
SQLisAwe5oMe.
March 7, 2016 at 10:11 am
You created your trigger on Orders but you posted the ddl for logger_all. Does the Orders table have the same columns??? The bigger issue is that your trigger has a MAJOR flaw. Even though it has been suggested repeatedly in this thread you still are assuming there will only ever be a single row in inserted. You need to handle multiple row operations. In this case it likely means a loop.
What does this return?
select Name
, [Version]
, [Timestamp]
from Orders
Also, you really should avoid using reserved words as column names. Especially reserved words that are other datatypes. It will drive you nuts!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2016 at 11:30 am
Sean Lange (3/7/2016)
You created your trigger on Orders but you posted the ddl for logger_all. Does the Orders table have the same columns??? The bigger issue is that your trigger has a MAJOR flaw. Even though it has been suggested repeatedly in this thread you still are assuming there will only ever be a single row in inserted. You need to handle multiple row operations. In this case it likely means a loop.What does this return?
select Name
, [Version]
, [Timestamp]
from Orders
Also, you really should avoid using reserved words as column names. Especially reserved words that are other datatypes. It will drive you nuts!!!
I am confused. I attached 2 different tables(dbo.Orders & dbo.Order_Meet)
Attaching them again, also including dbo.logger_all create script as well.
Regards,
SQLisAwe5oMe.
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply