After update trigger on a specific colomn

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

After update trigger on a specific colomn

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-03-31 : 12:29:10

I have a table called customers
and i want to send out an email whenever a new row is added or deleted
or when the fields customerid and customer_address have been updated.

I know how to do this on the table as a whole but not on specific coloumns.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:34:53

Create a trigger to check whether specific columns are updated and send mail. You can use If Update() or If columns_updated() function inside trigger to check if particular column is updated or not.

Harsh Athalye
http://www.letsgeek.net/

Kristen
Test

22859 Posts

Posted - 2010-04-01 : 03:41:46

"You can use If Update() or If columns_updated() function inside trigger to check if particular column is updated or not."

Note: only checks that that column was included in the UPDATE statement, not that that column has actually changed on any row(s) in the update - sadly

I am very weary of sending EMail in triggers - it can put you at the mercy of the response time of the SMTP service, which can be busy retrying an email to a non-existent / offline server ...

We add all Emails to a table, and then have a batch process just "take one and send it, repeat, ..." so that sending Email never causes a block

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:09:29

yeah...I second Kristen..Which also implement email functionality as a job which processes them as batches

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 06:18:48

quote:
Originally posted by Kristen

"You can use If Update() or If columns_updated() function inside trigger to check if particular column is updated or not."

Note: only checks that that column was included in the UPDATE statement, not that that column has actually changed on any row(s) in the update - sadly

I am very weary of sending EMail in triggers - it can put you at the mercy of the response time of the SMTP service, which can be busy retrying an email to a non-existent / offline server ...

We add all Emails to a table, and then have a batch process just "take one and send it, repeat, ..." so that sending Email never causes a block

That seems to be a nice strategy, Kristen. Thanks for that!

Harsh Athalye
http://www.letsgeek.net/

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-04-01 : 16:00:46

thank you all...
i am using coloumn_update function
and everything seems to work fine..actually my requirement has changed... i want to export my table to csv when there is an update.

I made the bcp command part of a job and all works fine but when i make an update from the front end asp apllication i get an error that @job'customeraddress' cannot be found : here is my sql code

alter TRIGGER updCustomer
ON INV_Customer
AFTER UPDATE AS

IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1) & 2 = 2)
or (SUBSTRING(COLUMNS_UPDATED(),4,1) & 4 = 4) )

BEGIN

EXEC msdb.dbo.sp_start_job N'customeraddress' ;

END
GO

i researched and found out that the user by which the asp application is accessing the sql should be a sysadmin.

is there any other method.

If i use this code in the

declare @prefix varchar(148)
set @PREFIX=REPLACE('\\C\Customer.csv','/','')
Declare @sql varchar(8000)
select @sql = 'bcp "Select ltrim(rtrim(customername)),ltrim(rtrim(customeraddress)) from test.dbo.Customer" queryout '+@PREFIX +' -c -t, -T -S' + @@servername
exec master..xp_cmdshell @sql

in my trigger in stead of putting this in a job the triegger is not completing...whats the best approach..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 16:10:26

did you notice Kristens reply to using COLUMNS_UPDATED?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2010-04-01 : 16:46:32

yes visakh .. i saw the post but my question is why is that the job containing the bcp command executes where as if make the query part of the trigger it does not compltete at all.. am i missing something in kirsten's reply...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources