asked
Update Trigger
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.
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 `AddressID`s 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]
@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;
@Oleg - If i were you, I would put some of these comments as separate answers - they deserve votes, very often...
@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.
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.
@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.
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.
@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.
