AFTER INSERT TRIGGER, with email

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

LVL 17
dbaSQLAsked:
Who is Participating?
 
dbaSQLConnect With a Mentor Author Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
Batch/bulk inserts are not applicable in this scenario.  Ever.

0
 
dbaSQLAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
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
 
lcohanDatabase AnalystCommented:
...c.status = 'DDDD'....

and what about DB collation/case sensitivity?
0
 
dbaSQLAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
dbaSQLAuthor Commented:
I appreciate the insight, lcohan.  I am very familiar with NOLOCK, and the pros and cons of using it.
0
 
dbaSQLAuthor Commented:
Still not entirely sure why the first version worked in one instance, yet not the other.  But, I got it with this one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.