locked
After Insert Trigger not running RRS feed

  • Question

  • I have a separate insert statement that inserts data for each hour of the day.  After the 24th hour has been inserted it is supposed to run this code:

    If (Select Hour from inserted) = 24 
    	Begin
    		EXEC msdb.dbo.sp_send_dbmail
        @recipients=N'abc.com',
    		@subject ='Automated Email (Do Not Respond) ',
    		@profile_name ='LBWL',
    		@query = 'Select Convert(nvarchar(17), Date, 110) as Date, Convert(nvarchar(16),HE) as [Hour Ending], Convert(nvarchar(12),sum(DA_CT)) as MW, Convert(nvarchar(12),DA_LMP$) as DA_LMP$
    		from 
    		(Select Date, HE, DA_CT, DA_LMP$ from ESP.dbo.Char_DA
    		union all
    		Select Date, HE, DA_CT, DA_LMP$ from ESP.dbo.Harb_DA
    		union all
    		Select Date, HE, DA_CT, DA_LMP$ from ESP.dbo.Lowe_DA
    		union all
    		Select Date, HE, DA_CT, DA_LMP$ from ESP.dbo.Peto_DA
    		)t
    		Where Date = DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 1)
    		Group By Date, HE, DA_LMP$ '
    		End
    

    This code does a select query and emails the results to the specified recipient.

    However the email never goes out and the 24th hour never gets inserted into the table.  It worked perfectly fine for months until i added this code so I know it has got something to do with "If (Select Hour from inserted) = 24" section of the code.  The code has to run after all 24 hours are inserted and seeing as I am inserting each hour one at a time, I can't just have the trigger run upon insert.

    Any assistance would be greatly welcomed.

    Sunday, January 2, 2011 4:18 AM

Answers

  • First, I suggested the change in the trigger's code that should have resolved the problem. Secondly, my suggestion is that trigger will populate EMails table when all 24 hours are inserted. The job will run out of the EMails table regularly (say, once in every 1 h.)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, January 2, 2011 7:43 PM
  • If there is only one insert, then perhaps the process inserting the record can also call a procedure to send/queue the email.  If the report email is critical, then perhaps a SQL Server job would be best.  A SQL Server job will have more options being separate from the task that inserts the record.  For example, an alert if the job fails, auto retries, etc.  You could create a service, but it would take a lot of work to be as reliable as the SQL Server Agent. 
    Randy in Marin
    • Proposed as answer by Naomi N Tuesday, January 4, 2011 11:20 PM
    • Marked as answer by tmacdonaldmpower Thursday, January 6, 2011 1:36 AM
    Tuesday, January 4, 2011 11:16 PM

All replies

  • have you tried running this code from outside of the trigger?
    Vishal Gajjar MCITP.DBAdministrator MCITP.DBDeveloper MCITP.BIDeveloper
    Sunday, January 2, 2011 4:47 AM
  • If (Select Hour from inserted) = 24 
    
    

    The above is not good coding for trigger since the SELECT may return several rows. You cannot assume that inserted table has a single row only.

    > the 24th hour never gets inserted into the table

    If the INSERT is not running, the trigger will not be triggered. Other possibility, the trigger (or other code ) has a ROLLBACK.

     


    Kalman Toth, Microsoft Community Contributor 2011; SQL 2008 GRAND SLAM
    Sunday, January 2, 2011 6:07 AM
  • Try changing condition to

    IF EXISTS (select 1 from Inserted where Hour = 24)

    Although it's a bad idea to run e-mail from trigger.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, January 2, 2011 6:49 AM
  • Hi tmacdonaldmpower,

    Can you show us the design of the table that the trigger is defined
    on? (Please post as a CREATE TABLE statement).
    And the INSERT statement that adds data every hour


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, January 2, 2011 11:23 AM
  • > If (Select Hour from inserted) = 24

    This code will bomb with an error if more than one row is ever inserted. Besides that, Hour sounds like something which is confined to the interval 0 to 23.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Sunday, January 2, 2011 11:37 AM
  • Hi ,

    There is no 24 hour format in SQL. You may perhaps wana check that. It eithere 12 or 00 . So after 23 it 00 and not 24.

    Regards,

    PS

     

    Sunday, January 2, 2011 11:37 AM
  • Yes i have tried running the code outside of the trigger.  Minus the "If (Select Hour from inserted) = 24" line of code and it works fine. 
    Sunday, January 2, 2011 7:03 PM
  • Naom,

    What would you suggest for me to do.  The email needs to go out after the 24 hours worth of data has been put into the table. It keeps on doing table locks on the 24th hour so the data for the 24th hour never gets inserted and therefore the email never gets sent out, my guess is because of the "If (Select Hour from inserted) = 24".


    I could run a sql job to send out the email but that would not guarentee that all 24 hours worth of day was inserted.

    Any suggestions?

    Sunday, January 2, 2011 7:06 PM
  • The Hour column of the table goes from 1 to 24 with 00 being 24. 

    Sunday, January 2, 2011 7:09 PM
  • the Hour column is Tinyint not datetime or smalldatetime.

    Sunday, January 2, 2011 7:10 PM
  • First, I suggested the change in the trigger's code that should have resolved the problem. Secondly, my suggestion is that trigger will populate EMails table when all 24 hours are inserted. The job will run out of the EMails table regularly (say, once in every 1 h.)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, January 2, 2011 7:43 PM
  • >Minus the "If (SelectHour from inserted) = 24" line of code and it works fine. 

    So obviously, the problems lies in that line. Have you already tried
    the suggestions posted by several people?

    If they all failed, then we obviously don't have sufficient
    information to answer your question. So allow me to repeat:

    Can you show us the design of the table that the trigger is defined
    on? (Please post as a CREATE TABLE statement).
    And the INSERT statement that adds data every hour


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Sunday, January 2, 2011 9:55 PM
  • I will give your suggestion a try when I get back into the office tomorrow.
    Sunday, January 2, 2011 10:15 PM
  • The Hour column of the table goes from 1 to 24 with 00 being 24. 

    Then, the condition should be 

    IF EXISTS (SELECT 1 FROM INSERTED WHERE HOUR = 0)
    

     


    Vishal Gajjar MCITP.DBAdministrator MCITP.DBDeveloper MCITP.BIDeveloper
    Monday, January 3, 2011 1:58 AM
  • Actually no it wouldn't, as i stated eariler.  The Hour column is a TinyInt not a Datetime field, you are looking at that column as a DateTime or SmallDateTime field when it is not.

     

    Tuesday, January 4, 2011 4:34 PM
  • Several thoughts....

    Is it okay to rollback the insert if the email fails? 

    Is it okay to not send the email if the insert is rolled back? 

    Will there be only one insert with hour 24 for any one report? 

    Are there time gaps between the 24 hour periods reported on? 

    Can the report be sent a few minutes or hours after the insert of the record with hour 24? 

    Can the trigger log the date range to report upon instead?  A separate job can monitor a table for a new date range and email a report and mark the record as processed? 

    Perhaps the insert trigger is not needed if the job can detect the hour 24 record and determine the date range from it (a field with a default GETDATE value)?  The job could track which records have been processed to prevent re-sending a report?  Also, I've learned the hard way to insure that something will limit how often email is sent. 

    If your tables are large, I would try moving the where clause into each select before doing the union.  I don't know if SQL will optimize this for you.  Perhaps adding a group by in each query as well with the outer group by getting the final sum?  If this report is slow, then it should not be in the trigger.  I would suggest testing it's logical io and cpu time to be safe. 


    Randy in Marin
    Tuesday, January 4, 2011 9:29 PM
  • To respond to your questions.

    1.  No it is not okay to rollback the insert if the email fails, this is critical data that needs to be inserted.

    2.  The email that is sent gets production data for the next day from the insert, so the email needs to go out.

    3.  There will only be 1 insert with hour 24 for any one report.

    4.  No, the 24 hours that are reported on are the 24 hrs for each day of the week.

    5.  Yes the report can be sent a few minutes after the insert of the record with hour 24, but not much longer.

    6.  This is a possiblility that I am looking into.

    7.  The insert trigger might not be needed for the email but it is definately needed for the insert of the 24 hrs worth of data.

    8.  The tables are relatively small,, so I don't think this is a concern at the moment.

    Tuesday, January 4, 2011 9:40 PM
  • 1.  No it is not okay to rollback the insert if the email fails, this is critical data that needs to be inserted.

    2.  The email that is sent gets production data for the next day from the insert, so the email needs to go out.

    I sense a contradiction here.

    If the mail cannot be put on the Service Broker queue, the trigger fails and the statement is rolled back. Then again, maybe that is just as well, because if the mail is not put on the queue, the mail will not be sent.

    But I think the real question is a trigger really a good solution? Maybe it is better to have a client to poll the table and send the mail? Or an Agent script?

    It is very difficult to tell from where I sit, because I only no as much you tell us. The original question was why the trigger did not work, and it was definitely not correctly written. But sometimes these small errors, reveal errors in the more larger design.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online: SQL 2008, SQL 2005 and  SQL 2000.
    (Just click the link you need.)
    Tuesday, January 4, 2011 10:31 PM
  • If there is only one insert, then perhaps the process inserting the record can also call a procedure to send/queue the email.  If the report email is critical, then perhaps a SQL Server job would be best.  A SQL Server job will have more options being separate from the task that inserts the record.  For example, an alert if the job fails, auto retries, etc.  You could create a service, but it would take a lot of work to be as reliable as the SQL Server Agent. 
    Randy in Marin
    • Proposed as answer by Naomi N Tuesday, January 4, 2011 11:20 PM
    • Marked as answer by tmacdonaldmpower Thursday, January 6, 2011 1:36 AM
    Tuesday, January 4, 2011 11:16 PM
  • It seems that either I didn't state it clearly or everyone is missing this.  The insert of the 24th hour of that day is not being inserted due to the email trigger timing out and locking the table, however hours 1 thru 23 are being inserted.  I will look into possibly setting up a SQL Agent job that checks to see if the data that is supposed to be inserted has been inserted and then have it run the email scripts from there.

    Thanks for your assistance. 

    Wednesday, January 5, 2011 2:18 PM
  • I am going to take your and Naom's suggestion and create a Sql Server job.

     

    Thursday, January 6, 2011 1:36 AM