Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vmon
Yak Posting Veteran
63 Posts |
 Posted - 2010-03-15 : 15:08:46
|
| I have an update trigger that sends an email when a record has been updated. Is there a way to identify what columns are different or have changed so I may include them in the email?Thanks in advance,John |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-03-15 : 15:10:03
|
| U mean the structure or the data in the columns?PBUH |
 |
|
|
vmon
Yak Posting Veteran
63 Posts |
Posted - 2010-03-15 : 15:14:10
|
| The data. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 00:32:44
|
you can . just check for each columns likeSELECT other details,...CASE WHEN ISNULL(i.column1,-1) <> ISNULL(d.column1,-1) then i.column1 else NULL end,CASE WHEN ISNULL(i.column2,'') <> ISNULL(d.column2,'') then i.column2 else NULL end,...FROM INSERTED iJOIN DELETED dON i.PK=d.PK.. PK is primary key------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-16 : 04:43:47
|
" I have an update trigger that sends an email when a record has been updated."Just want to point out that this is not normally a good idea!Couple of reasons:A trigger should be a very fast process, generating an EMail to SMTP server tends to be relatively slow. This does not matter if the number of updates on the table is tiny ...... which leads me to my next point, what happens if, unexpectedly, there are 10,000 updates to that table in a day? Its a lot of Emails in your inbox! so the solution does not scale very well.Clients usually ask us for "Can we have an email whenever there is a new order" ... bad design! ... email when the FIRST new order has been placed, and reset after someone has check the "New Orders" page, and maybe send an EMail every hour with the number of new orders waiting ...... or use a batch system, so that a process runs, say, every hour looking for new orders and sending an email.Beware what you put in the email too ... we have clients who want the details from any Contact Us page in the email. Customers often put "I used the wrong credit card, can you change to use 4111 ...." which is fine-(ish!) if entered on a Contact Us page with SSL, but is definitely not safe to transmit by email  Probably none of this is relevant to your use-case, but I just though I would raise it. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-03-16 : 07:10:43
|
| You should look up COLUMNS_UPDATED (http://msdn.microsoft.com/en-us/library/ms186329.aspx) or IF UPDATE(columnname) (http://msdn.microsoft.com/en-us/library/ms187326.aspx) to check if your columns have changed. Besides that, I second everything Kristen said.- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-16 : 09:35:03
|
"to check if your columns have changed"Beware that that only checks that the column was included in the UPDATE statement - not that the content has actually changed  - although it is an efficient means of ignoring a whole section of logic if the column was absent, its still worth checking that the content has actually changed (by joining INSERTED and DELETED pseudo-tables) |
|
|
|
|
|
|
|
|
Identify which columns changed