Update Trigger

Question

New to SQL, so bear with me please guys. I have a table 'ORDERS' that contains a field 'Comit'. When the Comit field is updated, if the field is set to 1 (field is of type bit) then I wish to send an email. I'll figure the email bit later. I just want to know how to test the value of the Comit field beforehand in a trigger. Thanks in advance.

Answer 1

0

Seeing as I don't have schema, this is how you would detect that AddressLine1 changed on the Person.Address table in AdventureWorks2008, and insert the relevant AddressIDs into another table called destination_table

CREATE TRIGGER trig_Address_update ON [Person].[Address]
FOR UPDATE 
AS 
INSERT INTO destination_table (AddressID)
SELECT [i].[AddressID] 
  FROM [inserted] i INNER JOIN 
       [deleted] d
    ON [i].[AddressID] = [d].[AddressID]
 WHERE [i].[AddressLine1] != [d].[AddressLine1]

answered Jul 27 '10 at 09:25 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

@Richard Burke Matt's answer should be enough to get you going in the right direction, but to clarify, you should test for 2 conditions:

 1. the Comit column was set to 1 by the update  2. the original (old) value was equal to 0

Otherwise, you might erroneously grab the updated record where Comit was subjected to update from 1 to 0. If the Comit column is nullable then updating from null to 1 or from 0 to 1 satisfy your criteria, and therefore, your predicate should probably read

where isnull(d.Comit, 0) = 0 and i.Comit = 1;

Please forgive me if I am going into details too deep, but since you mentioned that your are new to SQL, inserted and deleted refer to logical tables which are available to the triggers and output clauses. Deleted has all records affected by the update statement with old values, inserted has the same records with new values. Update trigger fires once per update statement, not per record, so if update affects 10 records, each logical table has 10 records in it. There is a discussion about update here: http://ask.sqlservercentral.com/questions/16288/update-under-the-hood

Jul 27 '10 at 10:45 AM Oleg

@Oleg - If i were you, I would put some of these comments as separate answers - they deserve votes, very often...

Jul 27 '10 at 10:54 AM Matt Whitfield ♦♦

@Matt, I'm sure you have a good reason, but I was just wondering why you use square brackets, [ ], around table names, fields and aliases.

Jul 27 '10 at 01:50 PM Mark

@Mark - because my editor does it for me :) That mode can be on or off, but I like it because it makes it explicit as to what the word is. For example, there is a filegroup called PRIMARY, but also a reserved word called PRIMARY (part of PRIMARY KEY). There are lots of fields called 'name' - but NAME may well become a reserved word in the future (it's already used in the EXTERNAL NAME clause for CLR object creation). And there's a lot of habit there too.

Jul 27 '10 at 02:03 PM Matt Whitfield ♦♦

@Matt - I see, that makes perfect sense. It is safer that way. I see fields names that are also commands often.

Jul 27 '10 at 02:22 PM Mark

(comments are locked)

Answer 2

0

Never send email from a trigger. Ever. There are far too many problems with that approach and no advantages.

So I would question why you need a trigger at all in this scenario. How about writing a process which polls the table periodically and sends out the emails? Triggers should generally be used as a last resort. It's much better in most cases to put logic in regular procedures and manage all your data access through those procedures.

answered Jul 27 '10 at 12:20 PM

David 1
1.8k ● 1 ● 3

@dportas This makes sense of course, but I believe that Richard is not planning to actually send email from the trigger. The trigger will probably just write into some table which will then be subjected to polling process which will actually send email (and probably delete processed records). The question is whether to use the trigger or opt for an output clause instead, but this is just a design consideration, either way will do.

Jul 27 '10 at 01:16 PM Oleg

Unfortunately, I am planning to send an email as a response to this trigger, in this case an order being placed. My ASP.net site coupled to a SQL back end sits on a shared server and I'm unsure at the moment if they'll support sheduled jobs, so triggers are my only option.

Jul 28 '10 at 01:47 AM koptastic69

@koptastic69 - whoah. That would suck quite hugely. I, like Oleg, had assumed you would be building a table with 'people to send email to later'. If you can find a way to avoid sending email directly from the trigger, then do so.

Jul 28 '10 at 02:40 AM Matt Whitfield ♦♦

(comments are locked)

Update Trigger

Follow this question

Related Questions