Solved

AFTER INSERT TRIGGER, with email

Posted on 2011-02-17
20
943 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8
20 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 34919515
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
ID: 34919585
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 40

Expert Comment

by:lcohan
ID: 34919851
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 17

Author Comment

by:dbaSQL
ID: 34919926
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
ID: 34919983
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 40

Expert Comment

by:lcohan
ID: 34920154
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
ID: 34920351
Batch/bulk inserts are not applicable in this scenario.  Ever.

0
 
LVL 17

Author Comment

by:dbaSQL
ID: 34920558
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 40

Expert Comment

by:lcohan
ID: 34920780
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 40

Expert Comment

by:lcohan
ID: 34920783
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 40

Expert Comment

by:lcohan
ID: 34920794
...c.status = 'DDDD'....

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

Author Comment

by:dbaSQL
ID: 34920860
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 40

Expert Comment

by:lcohan
ID: 34920960
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
ID: 34921066
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
ID: 34921413
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
ID: 34921972
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
ID: 34922048
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 40

Expert Comment

by:lcohan
ID: 34925786
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
ID: 34925960
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
ID: 34959098
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 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