EventArgs on Triggers?

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.

  else

end

This essentially what I want to do. But I would like to do this with TSQL, in a trigger.

Not sure if I understand what your asking, but you know about the inserted and deleted tables in the trigger?

I wanted to know WHAT values where in the ROW that was added to the TABLE that had the TRIGGER attached to it

So if you fill out a form:

Name: Jonathan Sampson
Age: 24
Height: 5'11
Email: johnsmith@people.com

And insert it into tbl_people, which has an AFTER insert trigger tied to it called "Notify_Person_of_New_Account" - I would like to send an email to the email address inserted with new row.

To complicate matters even more, I would like to ONLY send the email if the users email address isn't found within another table, called "tbl_blocked_emails".

I hope that makes sense; sorry if I was unclear on any part.

You could use the inserted table in your trigger to get the values that were inserted in the row.

Example:

Assuming you have a table with the following columns:
 ID[int], EmailAddress [varchar(2000)], EmailCount [int]

The trigger:

CREATE TRIGGER SampleTrigger
ON MyTable
AFTER INSERT
AS

DECLARE @ID int
DECLARE @MessageCount int
DECLARE @EmailAddress varchar(2000)

SELECT
    @ID = ID,
    @EmailAddress = EmailAddress
FROM
    inserted

SELECT @MessageCount = COUNT ('C') FROM Messages WHERE EmailAddress = @EmailAddress

IF (@MessageCount > 10)
BEGIN
    -- Modify email and send message
ELSE
BEGIN
    -- Fallback do something else
END

Nutcase,

Thank you - that seems like it will work. I'll update this thread later with progress if necessary.

Jonathan

This is interesting...my Trigger seems to be halting following calls to sprocs from my code-behind.

I've got a method called "MoveToNew()" which migrates records from a temporary table over to a permanent table. The permanent table has a trigger (AFTER INSERT) that emails the person with ownership of the newly migrated records.

In the codebehind, after the records are inserted (within the MoveToNew() method), a second call to another sproc is made to move children records from a temp table over to a permanent table (still taking place within MoveToNew()), but now these records aren't getting inserted...

Is it possible for a trigger to cause this?