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.
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),GETDAT
CREATE TRIGGER tr_tableB_calltime ON dbo.tableB
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'