Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

EventArgs on Triggers?

Back to Forum: Tech Off
  • User profile image
    jsampsonPC

    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.

  • User profile image
    PerfectPhase

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

  • User profile image
    jsampsonPC

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

    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.

  • User profile image
    NuTcAsE

    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


  • User profile image
    jsampsonPC

    Nutcase,

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

    Jonathan

  • User profile image
    jsampsonPC

    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?

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.