Solved

AFTER INSERT TRIGGER, with email

Posted on 2011-02-17
20
929 Views
Last Modified: 2012-05-11
I've created the trigger below to address a problem with NULL calltimes in tableB

tableA and tableB are joined by EventID -

record is inserted to tableA -
record is inserted to tableB -
   if tableB.calltime is Null, i need to check tableA.status, for the same eventID.
   if tableA.status = DDDD, I need to update the NULL tableB.calltime to two hours from now


All that is well and good, but I'd like to get an email if/when this update occurs.  I'm wondering if i need to change that to an IF EXISTS on the condition first, perform the update if needed, and then send an email.

But, I don't want to get too busy with the triggered event.

Any suggestions?

Also, because the action is AFTER INSERT, it is on new data only, as desired.  Because I am joining to inserted, there is no need to put a datetime in my where clause, like  this, is there?

WHERE c.call_starttime >= CONVERT(VARCHAR(10),GETDATE(),101)
CREATE TRIGGER tr_tableB_calltime ON dbo.tableB
AFTER INSERT 
AS 
UPDATE cd
SET calltime = DATEADD(HH,2,GETDATE()) 
FROM dbo.tableB cd INNER JOIN inserted i 
  ON cd.tableBid = i.tableBid JOIN dbo.tableA c 
    ON cd.eventID = c.eventID
  WHERE cd.calltime IS NULL
  AND c.status = 'DDDD'

Open in new window

0
Comment
Question by:dbaSQL
  • 12
  • 8
20 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
I would create another trigget ON UPDATE to fire only ON UPDATE (calltime) and where SELECT from INSERTED is NOT NULL and SELECT from DELETED IS NULL then send an email.

In other words the update trigger would fire only IF calltime was changed from NULL to NOT NULL value..
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Sorry, I'm not following.  The triggered update on calltime should only occur in the conditions I referenced --

Where the same eventID in tableA has a status of DDDD, and the inserted record into tableB has a null calltime.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
do the insert trigger will update the column as follow:

UPDATE cd SET calltime = DATEADD(HH,2,GETDATE())......

And what I meant is that an additional UPDATE trigger on the same table could send the email if data changed from NOT NULL to a value - something like like below


CREATE TRIGGER [calltimeUpdate] ON  [dbo].[YourTable]
   AFTER UPDATE
AS
BEGIN

-- stop the recursive trigger from firing more than once ...
IF ((SELECT TRIGGER_NESTLEVEL()) > 1 )
BEGIN
   RETURN
END

IF UPDATE(calltime)
BEGIN
            IF (SELECT calltime FROM deleted) IS NULL AND (SELECT calltime FROM inserted) IS NOT NULL
                  BEGIN                                      
                        --send mail code here
                        DECLARE @sqlstr varchar(1000)
                        SET @sqlstr = 'CallTime alert Occured at: '+char(13)+cast(getdate() as sysname);
                        EXEC msdb.dbo.sp_send_dbmail
                              @profile_name = 'yourmailprofile',
                              @recipients = 'you@email.com',
                              @subject = 'CallTime alert',
                              @body =  @sqlstr
                  END
END
END
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
The after insert trigger is the only necessity.  The calltime data is not updated, ever, except for right now because it sometimes goes in as NULL, and needs to be corrected.

NOT NULL calltime values are not updated.

I modified the construct that I posted earlier, to this.  It works.  I'm just wondering if it is as optimal as it could be.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER triggername ON tableB
AFTER INSERT
AS
IF EXISTS (SELECT 1
           FROM tableB cd JOIN inserted AS i 
             ON cd.tableBid = i.tableBid JOIN tableA c 
		ON cd.eventID = c.eventID
           WHERE cd.calltime IS NULL 
           AND c.status = 'DDDD'
           )
	BEGIN
		UPDATE cd
		SET calltime = DATEADD(hh,2,GETDATE())
		FROM tableB cd JOIN inserted AS i 
                  ON cd.tableBid = i.tableBid JOIN tableA c 
		    ON cd.eventID = c.eventID
           WHERE cd.calltime IS NULL 
           AND c.status = 'DDDD'
           
           EXEC msdb..sp_send_dbmail
		................
		....
       
	   IF @@ERROR <> 0
	   BEGIN
	  	   RAISERROR('Failure updating the NULL calltime.',16,1)
		   RETURN;
	   END
	END

GO

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I also think the event notification is mute.  Looking at the data, there is no way to tell which values came in correctly, or which ones were updated by the trigger.  Needless overhead, I'm betting, on my trigger.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
That should be fine assuming you have index on

tableB.tableBid
tableA.eventID,status

I would suggest add missing NOLOCK hints to all your FROM and JOINS and add the INNER to the JOIN even if is implicit. Also did you thought about posibility that batch/bulk inserts firing this trigger?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Batch/bulk inserts are not applicable in this scenario.  Ever.

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I inserted a bunch of test data into tableA in the dev bed, meeting the above condtions.
I then inserted corresponsing records into tableB, with the same eventID, where calltime was NULL.

It updated the calltime as expected.  (I kept the email notification in there, to be sure.)
So then I enabled the same logic in production.

It is not triggering the update.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You should check for condition below to be true - for some reason that is not in PROD versus DEV. Do you have same Isolation Level and recovery modes in both?
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
IF EXISTS (SELECT 1
           FROM tableB cd JOIN inserted AS i
             ON cd.tableBid = i.tableBid JOIN tableA c
            ON cd.eventID = c.eventID
           WHERE cd.calltime IS NULL
           AND c.status = 'DDDD'
           )
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 39

Expert Comment

by:lcohan
Comment Utility
...c.status = 'DDDD'....

and what about DB collation/case sensitivity?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
collation and case sensitivity are not an issue, they are the same on both instances.

I have checked that condition numerous times in production, which is why I know that it is not triggering the update.  I am watching the records meeting that condition increase, rather than remain the same, because the update would be running, and the calltimes wouldn't be NULL.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
Sorry I wasn't clear...the condition bellow is NOT true that's why the update is NOT done. Do you have multiple triggers on the table? If yes I suggest you set this one LAST:
....
IF EXISTS (SELECT 1
           FROM tableB cd JOIN inserted AS i
             ON cd.tableBid = i.tableBid JOIN tableA c
            ON cd.eventID = c.eventID
           WHERE cd.calltime IS NULL
           AND c.status = 'DDDD'
           )
.....
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
As I said, the condition is true.  I am looking at the data right now, there are values between the tables where tableA.status = DDDD and tableB.calltime is NULL.

There is only one trigger on the table, it is the one I have referenced above.

Also as I said, before I enabled it in prod, I copied a bunch of data from prod to dev, and simulated exactly the same construct.  The values were updated.

I just did it again in dev, it worked, calltime was updated, i got an email.



0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Correction.  When I said I was looking at the data, I was looking at this:

SELECT....
FROM tableB cd INNER JOIN tableA tc
ON cd.eventID = tc.eventID
WHERE cd.timestamp >= CONVERT(CHAR(8),GETDATE(),112) --- just to target only current day
AND cd.calltime IS NULL
AND tc.status = 'DDDD'

Of course, I was not looking at the above with a join to inserted, because that only applies to the inserts.

But, the trigger is there, data is being written, and the condition is met -- it's just that the calltime is not being updated.


very interesting, the same exact construct works on the dev bed.  clearly i've missed something.
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Well, it has to be something in the method their using to write the data into the tables.  I've just done exactly the same test on prod, writing garbage data, and it worked.  The trigger was called, the calltime was updated.

I very recently inherited these databases, and I am still getting my arms around the infrastructure.  I have searched string on every object in that database, there is no formal insert procedure into either table.

That shouldn't matter, though, as I am not using an insert procedure in my testing -- i'm just doing the insert ad hoc, and it fires the trigger.

I'm not sure what it is.  My test in production with garbage data invoked the trigger fine.  Their active insertions are not firing the trigger.
0
 
LVL 17

Accepted Solution

by:
dbaSQL earned 0 total points
Comment Utility
I changed it to the routine below, it's working now.  I do not understand why the previous version worked on one instance, yet not the other.

The email notification is only there to let me know if it's running.  It is.  I will comment that out now.
CREATE TRIGGER triggername
ON tableB
AFTER INSERT
AS
BEGIN
    UPDATE cd
    SET calltime = DATEADD(hh,2,GETDATE())
    FROM tableB cd JOIN inserted AS i 
      ON cd.tableBid = i.tableBid JOIN tableA c 
        ON cd.eventID = c.eventID
    WHERE cd.calltime IS NULL 
    AND c.status = 'DDDD'
/*
   EXEC msdb..sp_send_dbmail
   ............
*/
END

Open in new window

0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
As I said - previous version did not worked because
...
IF EXISTS (SELECT 1
           FROM tableB cd JOIN inserted AS i
             ON cd.tableBid = i.tableBid JOIN tableA c
            ON cd.eventID = c.eventID
           WHERE cd.calltime IS NULL
           AND c.status = 'DDDD'
           )
...

evaluated to false for whatever reason you could not find but it doesn't matter as long as you found the working version. Still I suggest look in SQL docs and add the missing WITH (NOLOCK) clauses to all the FROM and JOINS to save you from trouble blocking/locking. Good luck!

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
I appreciate the insight, lcohan.  I am very familiar with NOLOCK, and the pros and cons of using it.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
Comment Utility
Still not entirely sure why the first version worked in one instance, yet not the other.  But, I got it with this one.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now