x

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.
more ▼

asked Jul 27, 2010 at 09:15 AM in Default

koptastic69 gravatar image

koptastic69
59 5 5 5

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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]
more ▼

answered Jul 27, 2010 at 09:25 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 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, 2010 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, 2010 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, 2010 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, 2010 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, 2010 at 02:22 PM Mark
(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Jul 27, 2010 at 12:20 PM

David 1 gravatar image

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, 2010 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, 2010 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, 2010 at 02:40 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x129
x117

asked: Jul 27, 2010 at 09:15 AM

Seen: 1255 times

Last Updated: Jul 27, 2010 at 09:15 AM