Suppose I create a trigger that runs AFTER INSERT on a table called 'messages'. What I want the trigger to do is send an email to the person that inserted the record, which is identified by an email address in the record that was submitted.
To extend this out a little bit more, I would like to check another table (based upon the email address found in our recently-added row) to see how many messages the user has sent. If it's greater than 10, I would like to change the message in the email slightly.
So at this point. I'm lost. I know how to make a trigger (thanks, MSDN). But I don't know if there is a way to get the details from the row that spawned the trigger-call. Maybe I need to use @@IDENTITY? (I read that this isn't suggested, but instead to use SCOPE_IDENTITY() in its place?) But how well does that work if I have dozens of messages being sent every few seconds? Could @@IDENTITY actually pull details from somebody else's message?
Here's some pseudo-code to explain what I want to do.
event: new row inserted
theRow = RowInserted;
theEmail = theRow.EmailAddress;
totalMessages = SELECT messages FROM tbl2 WHERE id = theEmail;
if totalMessages > 10 then
body = "Too many messages, slow down.";
sendEmail to theEmail containing the body.
This essentially what I want to do. But I would like to do this with TSQL, in a trigger.