March 9, 2016 at 9:56 am
Removed
March 9, 2016 at 10:39 am
SQLisAwE5OmE (3/9/2016)
Sean Lange (3/9/2016)
SQLisAwE5OmE (3/9/2016)
Sean Lange (3/9/2016)
SQLisAwE5OmE (3/9/2016)
When I purchased 2 programs(classes) in the same order, I got 2 separate emails. So, is that mean it's working?No. That means that your program inserts the rows one at a time. All insert statements are NOT single row. You need to send an email for EACH row in inserted. This means looping. This is one of the few times where you need to loop. Strangely it is usually difficult to get people to not loop. In this case I can't seem to convince you that you need to. 😉
You mean I don't need to worry about multiple row inserts?
You DO need to worry about them. That is what I have said now almost 10 times in this thread!!! Just because the app doesn't today does NOT mean it won't in the future. And it is not the only way rows can be inserted into that table. You could very easily write an insert statement yourself right??? This is turning into an episode of "Who's on first?".
I only need to worry about online registrations....dbo.order table....column name(Source_Code = WEB)....I need to specify that also in the script.
Any manual registration, I don't need to get notifications.
OK. Since you are dead-set against doing this correctly what is the problem? You are getting emails right?
_______________________________________________________________
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 9, 2016 at 10:41 am
SQLisAwE5OmE (3/9/2016)
I have modified my trigger to do a join with another table to get the Meeting(Class) code since it doesn't appear on dbo.order table, the meeting code is inserted to dbo.order_meet table.However, after doing this, when I register for a class, I am getting blank email...can you point out if I my join is correct in the code, or where exactly I am off..
USE [IMIS_TEST]
GO
/****** Object: Trigger [dbo].[Orders_Online_Reg] Script Date: 03/09/2016 09:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @ID varchar(10)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Timestamp varchar(100)
Declare @Meeting varchar(15);
if exists (select * from inserted)
BEGIN
set @Subject = 'New online registration occurred'
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Meeting = om.Meeting, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE
from inserted i
join dbo.order_meet om on i.ORDER_NUMBER=om.ORDER_NUMBER
select @Body = 'Following are the details: ' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10)
+ 'ORDER_NUMBER : ' + @ORDER_NUMBER + CHAR(13)+CHAR(10)
+ 'ID : ' + @ID + CHAR(13)+CHAR(10)
+ 'Name : ' + @Name + CHAR(13)+CHAR(10)
+ 'Meeting : ' + @Meeting + CHAR(13)+CHAR(10)
+ 'TimeStamp : ' + @Timestamp
from inserted i ;
INSERT INTO dbo.logger_all SELECT @Body, @ORDER_NUMBER, @ID, @Subject, @Name, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'DefaultProfile',
@recipients = 'Some@domain.com',
@subject = @subject,
@body = @body
END
This is because at least one of your variables is NULL. Then when you concatenate them together you get NULL because that is how NULL works. You could wrap each variable in ISNULL(@variable, '')
_______________________________________________________________
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 9, 2016 at 11:16 am
Sean Lange (3/8/2016)
MMartin1 (3/8/2016)
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATEfrom inserted i
This above will fail whenever the application inserted more than one row as part of the operation. The table [Inserted] will return more than one row in these situations.
Whenever you get data into your table, the job for the trigger should be as simple as something like
INSERT into MyLoggingTable(colA, colB, colC, colD, updatedOn)
SELECT colm1, colm3, colm7, colm8, getdate() /* just the columns you need */
FROM Inserted
To add to what Martin is saying here. It will fail but only logically. It will NOT throw an error. It will set your variables values to the last row in the result set of your query. If your inserted table contains 2 rows you don't even know which one of the two you will get. This why I keep harping on making this set based.
What Sean is trying to tell you in all the posts since this, is that you are thinking about a row by row operation. This is not how triggers work. If I insert 10 rows into a table with one insert statement, the trigger will start only after the tenth row is in there. Then my [Inserted] table will have those ten rows. From here, try to model your trigger to use that [Inserted] table as I show in the example above. It should be an ordinary SQL set based statement, no looping. Give it a try.
----------------------------------------------------
March 9, 2016 at 11:20 am
To add to the post above ... somewhere I've read that Oracle databases apply triggers on a row by row basis, or at least can. So I was thinking if you are coming from a Oracle background that this might be a point of confusion. It is not how SQL Server does it.
----------------------------------------------------
March 10, 2016 at 4:09 am
The OP might like to look at the following article:
https://www.mssqltips.com/sqlservertip/2231/avoid-external-dependencies-in-sql-server-triggers/
March 10, 2016 at 7:21 am
I am not trying to ignore you guys in terms fixing the code for multiple row inserts.....
But my trigger is not working as per my requirements, I am trying to get it work first before making modification to it.
Below is my code....
USE [IMIS_TEST]
GO
/****** Object: Trigger [dbo].[Orders_Online_Reg] Script Date: 03/09/2016 09:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @ID varchar(10)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Timestamp varchar(100)
Declare @Meeting varchar(15)
if exists (select * from inserted)
BEGIN
set @Subject = 'New online registration occurred'
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Meeting = om.Meeting, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE
from inserted i
join dbo.order_meet om on i.ORDER_NUMBER=om.ORDER_NUMBER
select @Body = 'Following are the details: ' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10)
+ 'ORDER_NUMBER : ' +ISNULL(@ORDER_NUMBER, '[Missing ORDER_NUMBER]') + CHAR(13)+CHAR(10)
+ 'ID : ' + ISNULL(@ID, '[Missing ID]') + CHAR(13)+CHAR(10)
+ 'Name : ' + ISNULL(@Name, '[Missing Name]') + CHAR(13)+CHAR(10)
+ 'Meeting : ' + ISNULL(@Meeting, '[Missing Meeting]') + CHAR(13)+CHAR(10)
+ 'TimeStamp : ' + ISNULL(@Timestamp, '[Missing Timestamp]')
from inserted i ;
INSERT INTO dbo.logger_all SELECT @ID, @ORDER_NUMBER, @Subject, @Body, @Name, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'DefaultProfile',
@recipients = 'Some@domain.com',
@subject = @subject,
@body = @body
END
And when I test this, I am getting email with the following body.
Subject: New online registration occurred
Following are the details:
ORDER_NUMBER : [Missing ORDER_NUMBER]
ID : [Missing I
Name : [Missing Name]
Meeting : [Missing Meeting]
TimeStamp : [Missing Timestamp]
Let me breakdown my requirement again.
-The depts need an email notification for every online registration
-They want to know, ID, Name, Order#, OrderDate......these are part of dbo.orders table
-They also want Class Code(Meeting column)....and this detail is inserted into dbo.order_meet table.
So, I need to join the dbo.orders and order_meet to get the Meeting code
Let me know if you have questions.
Regards,
SQLisAwe5oMe.
March 10, 2016 at 7:44 am
SQLisAwE5OmE (3/10/2016)
I am not trying to ignore you guys in terms fixing the code for multiple row inserts.....But my trigger is not working as per my requirements, I am trying to get it work first before making modification to it.
Below is my code....
USE [IMIS_TEST]
GO
/****** Object: Trigger [dbo].[Orders_Online_Reg] Script Date: 03/09/2016 09:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @ID varchar(10)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Timestamp varchar(100)
Declare @Meeting varchar(15)
if exists (select * from inserted)
BEGIN
set @Subject = 'New online registration occurred'
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Meeting = om.Meeting, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE
from inserted i
join dbo.order_meet om on i.ORDER_NUMBER=om.ORDER_NUMBER
select @Body = 'Following are the details: ' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10)
+ 'ORDER_NUMBER : ' +ISNULL(@ORDER_NUMBER, '[Missing ORDER_NUMBER]') + CHAR(13)+CHAR(10)
+ 'ID : ' + ISNULL(@ID, '[Missing ID]') + CHAR(13)+CHAR(10)
+ 'Name : ' + ISNULL(@Name, '[Missing Name]') + CHAR(13)+CHAR(10)
+ 'Meeting : ' + ISNULL(@Meeting, '[Missing Meeting]') + CHAR(13)+CHAR(10)
+ 'TimeStamp : ' + ISNULL(@Timestamp, '[Missing Timestamp]')
from inserted i ;
INSERT INTO dbo.logger_all SELECT @ID, @ORDER_NUMBER, @Subject, @Body, @Name, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'DefaultProfile',
@recipients = 'Some@domain.com',
@subject = @subject,
@body = @body
END
And when I test this, I am getting email with the following body.
Subject: New online registration occurred
Following are the details:
ORDER_NUMBER : [Missing ORDER_NUMBER]
ID : [Missing I
Name : [Missing Name]
Meeting : [Missing Meeting]
TimeStamp : [Missing Timestamp]
Let me breakdown my requirement again.
-The depts need an email notification for every online registration
-They want to know, ID, Name, Order#, OrderDate......these are part of dbo.orders table
-They also want Class Code(Meeting column)....and this detail is inserted into dbo.order_meet table.
So, I need to join the dbo.orders and order_meet to get the Meeting code
Let me know if you have questions.
That means you are getting NULL values in your select statement. I would suspect that it is because of the inner join to ORDER_MEET. It seems highly unlikely that you would have a row in ORDER_MEET when you are entering the ORDERS. Think about the relationship there. ORDER_MEET joins on the primary key from ORDERS but your trigger is for an insert into the ORDERS table. At the time you trigger fires there aren't going to be any rows in ORDER_MEET. This suggests that you either need to change your logic and drop the columns from that table or rethink how this trigger needs to work. It will never work the way have this coded. I don't know the business requirements here but maybe the trigger belongs on another table? Is the requirement that the email be sent real time? If not, maybe a job that run every x minutes or hours to pick up the ORDERS that haven't yet been sent would work. I would prefer this type of process myself as it means not having triggers to deal with.
If you want to prove my theory about the order of operations here simply change your code to have a LEFT JOIN to ORDER_MEET and you will start getting ORDERS data but nothing from the other table. 😉
_______________________________________________________________
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 10, 2016 at 7:56 am
Sean Lange (3/10/2016)
SQLisAwE5OmE (3/10/2016)
I am not trying to ignore you guys in terms fixing the code for multiple row inserts.....But my trigger is not working as per my requirements, I am trying to get it work first before making modification to it.
Below is my code....
USE [IMIS_TEST]
GO
/****** Object: Trigger [dbo].[Orders_Online_Reg] Script Date: 03/09/2016 09:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @ID varchar(10)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Timestamp varchar(100)
Declare @Meeting varchar(15)
if exists (select * from inserted)
BEGIN
set @Subject = 'New online registration occurred'
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Meeting = om.Meeting, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE
from inserted i
join dbo.order_meet om on i.ORDER_NUMBER=om.ORDER_NUMBER
select @Body = 'Following are the details: ' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10)
+ 'ORDER_NUMBER : ' +ISNULL(@ORDER_NUMBER, '[Missing ORDER_NUMBER]') + CHAR(13)+CHAR(10)
+ 'ID : ' + ISNULL(@ID, '[Missing ID]') + CHAR(13)+CHAR(10)
+ 'Name : ' + ISNULL(@Name, '[Missing Name]') + CHAR(13)+CHAR(10)
+ 'Meeting : ' + ISNULL(@Meeting, '[Missing Meeting]') + CHAR(13)+CHAR(10)
+ 'TimeStamp : ' + ISNULL(@Timestamp, '[Missing Timestamp]')
from inserted i ;
INSERT INTO dbo.logger_all SELECT @ID, @ORDER_NUMBER, @Subject, @Body, @Name, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'DefaultProfile',
@recipients = 'Some@domain.com',
@subject = @subject,
@body = @body
END
And when I test this, I am getting email with the following body.
Subject: New online registration occurred
Following are the details:
ORDER_NUMBER : [Missing ORDER_NUMBER]
ID : [Missing I
Name : [Missing Name]
Meeting : [Missing Meeting]
TimeStamp : [Missing Timestamp]
Let me breakdown my requirement again.
-The depts need an email notification for every online registration
-They want to know, ID, Name, Order#, OrderDate......these are part of dbo.orders table
-They also want Class Code(Meeting column)....and this detail is inserted into dbo.order_meet table.
So, I need to join the dbo.orders and order_meet to get the Meeting code
Let me know if you have questions.
That means you are getting NULL values in your select statement. I would suspect that it is because of the inner join to ORDER_MEET. It seems highly unlikely that you would have a row in ORDER_MEET when you are entering the ORDERS. Think about the relationship there. ORDER_MEET joins on the primary key from ORDERS but your trigger is for an insert into the ORDERS table. At the time you trigger fires there aren't going to be any rows in ORDER_MEET. This suggests that you either need to change your logic and drop the columns from that table or rethink how this trigger needs to work. It will never work the way have this coded. I don't know the business requirements here but maybe the trigger belongs on another table? Is the requirement that the email be sent real time? If not, maybe a job that run every x minutes or hours to pick up the ORDERS that haven't yet been sent would work. I would prefer this type of process myself as it means not having triggers to deal with.
If you want to prove my theory about the order of operations here simply change your code to have a LEFT JOIN to ORDER_MEET and you will start getting ORDERS data but nothing from the other table. 😉
But I do see a row gets inserted into dbo.order_meet table with the same order_number.....see my screenshot. I believe this is happening parallel.
So, you are saying I still cannot use the join the trigger?
Let me know your thoughts.
Regards,
SQLisAwe5oMe.
March 10, 2016 at 8:04 am
SQLisAwE5OmE (3/10/2016)
Sean Lange (3/10/2016)
SQLisAwE5OmE (3/10/2016)
I am not trying to ignore you guys in terms fixing the code for multiple row inserts.....But my trigger is not working as per my requirements, I am trying to get it work first before making modification to it.
Below is my code....
USE [IMIS_TEST]
GO
/****** Object: Trigger [dbo].[Orders_Online_Reg] Script Date: 03/09/2016 09:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @ID varchar(10)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Timestamp varchar(100)
Declare @Meeting varchar(15)
if exists (select * from inserted)
BEGIN
set @Subject = 'New online registration occurred'
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Meeting = om.Meeting, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE
from inserted i
join dbo.order_meet om on i.ORDER_NUMBER=om.ORDER_NUMBER
select @Body = 'Following are the details: ' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10)
+ 'ORDER_NUMBER : ' +ISNULL(@ORDER_NUMBER, '[Missing ORDER_NUMBER]') + CHAR(13)+CHAR(10)
+ 'ID : ' + ISNULL(@ID, '[Missing ID]') + CHAR(13)+CHAR(10)
+ 'Name : ' + ISNULL(@Name, '[Missing Name]') + CHAR(13)+CHAR(10)
+ 'Meeting : ' + ISNULL(@Meeting, '[Missing Meeting]') + CHAR(13)+CHAR(10)
+ 'TimeStamp : ' + ISNULL(@Timestamp, '[Missing Timestamp]')
from inserted i ;
INSERT INTO dbo.logger_all SELECT @ID, @ORDER_NUMBER, @Subject, @Body, @Name, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'DefaultProfile',
@recipients = 'Some@domain.com',
@subject = @subject,
@body = @body
END
And when I test this, I am getting email with the following body.
Subject: New online registration occurred
Following are the details:
ORDER_NUMBER : [Missing ORDER_NUMBER]
ID : [Missing I
Name : [Missing Name]
Meeting : [Missing Meeting]
TimeStamp : [Missing Timestamp]
Let me breakdown my requirement again.
-The depts need an email notification for every online registration
-They want to know, ID, Name, Order#, OrderDate......these are part of dbo.orders table
-They also want Class Code(Meeting column)....and this detail is inserted into dbo.order_meet table.
So, I need to join the dbo.orders and order_meet to get the Meeting code
Let me know if you have questions.
That means you are getting NULL values in your select statement. I would suspect that it is because of the inner join to ORDER_MEET. It seems highly unlikely that you would have a row in ORDER_MEET when you are entering the ORDERS. Think about the relationship there. ORDER_MEET joins on the primary key from ORDERS but your trigger is for an insert into the ORDERS table. At the time you trigger fires there aren't going to be any rows in ORDER_MEET. This suggests that you either need to change your logic and drop the columns from that table or rethink how this trigger needs to work. It will never work the way have this coded. I don't know the business requirements here but maybe the trigger belongs on another table? Is the requirement that the email be sent real time? If not, maybe a job that run every x minutes or hours to pick up the ORDERS that haven't yet been sent would work. I would prefer this type of process myself as it means not having triggers to deal with.
If you want to prove my theory about the order of operations here simply change your code to have a LEFT JOIN to ORDER_MEET and you will start getting ORDERS data but nothing from the other table. 😉
But I do see a row gets inserted into dbo.order_meet table with the same order_number.....see my screenshot. I believe this is happening parallel.
So, you are saying I still cannot use the join the trigger?
Let me know your thoughts.
Of course you see it happen because you are using the application. Think about what the application does behind the scenes. These queries do not execute in parallel. That doesn't make any sense. The insert into ORDERS has to complete in order to get the foreign key values for ORDER_MEET. That means in the application there are two insert statements.
Insert ORDERS
Insert ORDER_MEET
Your trigger fires when the row is insert into ORDERS so there is no row yet in ORDER_MEET. You can't use an inner join in your trigger because that row does not yet exist. If you change it to a LEFT JOIN the variable being populated from ORDER_MEET would be NULL.
_______________________________________________________________
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 10, 2016 at 9:07 am
Yes, I tested your theory and this is what I get in my email body.
Following are the details:
ORDER_NUMBER : 116277.00
ID : 242078
Name : Mr. Tony Alexander
Meeting : [Missing Meeting]
TimeStamp : Mar 10 2016 12:00AM
But I read that you can do delay in your trigger as shown below, if I include the delay, would it work? and where to insert the delay command?
WAITFOR DELAY '00:00:10' ---- 10 Second Delay
USE [IMIS_TEST]
GO
/****** Object: Trigger [dbo].[Orders_Online_Reg] Script Date: 03/09/2016 09:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Orders_Online_Reg]
ON [dbo].[Orders]
AFTER insert
AS
DECLARE @ORDER_NUMBER varchar(100)
DECLARE @ID varchar(10)
DECLARE @Body varchar(500)
Declare @Subject varchar(104)
Declare @Name varchar(200)
Declare @Timestamp varchar(100)
Declare @Meeting varchar(25)
if exists (select * from inserted)
BEGIN
set @Subject = 'New online registration occurred'
SELECT @ORDER_NUMBER = i.ORDER_NUMBER, @Meeting = om.Meeting, @ID = i.ST_ID, @Name = i.FULL_NAME, @Timestamp = i.ORDER_DATE
from inserted i
LEFT JOIN dbo.order_meet om on i.ORDER_NUMBER=om.ORDER_NUMBER
select @Body = 'Following are the details: ' + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10)
+ 'ORDER_NUMBER : ' +ISNULL(@ORDER_NUMBER, '[Missing ORDER_NUMBER]') + CHAR(13)+CHAR(10)
+ 'ID : ' + ISNULL(@ID, '[Missing ID]') + CHAR(13)+CHAR(10)
+ 'Name : ' + ISNULL(@Name, '[Missing Name]') + CHAR(13)+CHAR(10)
+ 'Meeting : ' + ISNULL(@Meeting, '[Missing Meeting]') + CHAR(13)+CHAR(10)
+ 'TimeStamp : ' + ISNULL(@Timestamp, '[Missing Timestamp]')
from inserted i ;
INSERT INTO dbo.logger_all SELECT @ID, @ORDER_NUMBER, @Subject, @Body, @Name, @Timestamp
EXEC msdb..sp_send_dbmail
@profile_name = 'DefaultProfile',
@recipients = 'Some@Domain.com',
@subject = @subject,
@body = @body
END
Regards,
SQLisAwe5oMe.
March 10, 2016 at 9:15 am
You need to read up a lot about how triggers work. If you add a delay it is only going to delay the processing. That means your application will just wait until the trigger completes. Then your application will start again and do the insert to ORDER_MEET. Plain and simple here. There is no option in this trigger to get the data from the other table.
You need to do one of four things.
1) Accept this as it is and change the contents of your email.
2) Drop the trigger on this table. You might be able to do this on ORDER_MEET instead but I don't know what that table is in the big picture.
3) Rethink this entirely and find another way to accomplish this.
4) Hire a consultant to help you through this.
_______________________________________________________________
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 10, 2016 at 10:24 am
Sean,
If you don't mind, can you elaborate more on these steps.
Is the requirement that the email be sent real time?
Ans: No, I do not need it real time.
If not, maybe a job that run every x minutes or hours to pick up the ORDERS that haven't yet been sent would work. I would prefer this type of process myself as it means not having triggers to deal with.
Regards,
SQLisAwe5oMe.
March 10, 2016 at 10:51 am
SQLisAwE5OmE (3/10/2016)
Sean,If you don't mind, can you elaborate more on these steps.
Is the requirement that the email be sent real time?
Ans: No, I do not need it real time.
If not, maybe a job that run every x minutes or hours to pick up the ORDERS that haven't yet been sent would work. I would prefer this type of process myself as it means not having triggers to deal with.
Then this is super simple. You just need to write a query that will get ORDERS entered since the last time your job ran, or any ORDERS where you have not yet sent an email. You can do this a couple ways. Either have a table with the last run date of your process...if this is a sql job you can use the entry in sql already. Or you can create a table of all order ids you have sent an email for and query for orders that aren't in that table. Whichever way works for you. Then loop through those results sending an email for each 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/
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply