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.
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: email@example.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.
Assuming you have a table with the following columns: ID[int], EmailAddress [varchar(2000)], EmailCount [int]
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
Thank you - that seems like it will work. I'll update this thread later with progress if necessary.
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
Is it possible for a trigger to cause this?
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.