update trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » update trigger

update trigger

Rate Topic

Display Mode

Topic Options

Author

Message

sudhirbharti-565093

Posted Thursday, January 29, 2009 6:53 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 26, 2012 10:39 AM
Points: 19, Visits: 129

I am writing a trigger where i need to execute the db mail on the basis of field condition.
That means if a user update a field like city where city like '%p%' or city like '%s%' then it send the mail.

program :

create TRIGGER sendMail
ON [userinfo]
FOR UPDATE
AS

IF UPDATE (city)

BEGIN
declare @UserID varchar(10)
declare @body varchar(2000)
declare @OldCity varchar(10)
declare @NewCity varchar(10)

SELECT @UserID = userid,
@OldCity= d.City
FROM deleted d

SELECT @NewCity = City
FROM inserted

SET @body = 'User ID=' +@UserID+ ' has been updated with previous name is '+@OldCity+ ' and the new name is ' +@NewCity

Execute msdb.dbo.sp_send_dbmail
@profile_name = 'XYZManagement'
,@recipients = 'sbharti_22k1@rediffmail.com;sudhirbhart22i@gmail.com'
,@subject = 'user Information '
,@body = @body
,@query = NULL
,@importance = 'normal'
END
GO
----------------
Note : the trigger is working fine and it sending the mail when we update the city column. but i am unable to place the like statement by which it only send the mail if updated city started with 's' or 'p'

Any ideas?

Post #645858

GilaMonster

Posted Thursday, January 29, 2009 6:58 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

sudhirbharti (1/29/2009)

SELECT @UserID = userid,
@OldCity= d.City
FROM deleted d

SELECT @NewCity = City
FROM inserted

Ignoring the city problem for now, what's going to happen if more than one row is updated in a single update statement?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #645863

sudhirbharti-565093

Posted Thursday, January 29, 2009 7:07 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 26, 2012 10:39 AM
Points: 19, Visits: 129

thanks I haven't think about that, what i have to do for the multiple row.
Any ideas?

Post #645878

GilaMonster

Posted Thursday, January 29, 2009 7:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:17 PM
Points: 37,713, Visits: 29,968

What I'd suggest is, instead of sending the mail direct from a trigger (which is usually a bad idea anyway), have the trigger write the info for the mails into another table (call it PendingEmails or something) and have a job that runs regularly that checks that, iterates through any rows and sends any mails.

So then the trigger would just looks like this

IF UPDATE (city)
 insert into PendingEmails (UserID, OldCity, NewCity)
 SELECT i.userid, d.city, i.city FROM inserted inner join deleted ON ... (put the appropriate join based on the PK of the table)
 WHERE i,city != d,city -- don't catch cases where the city has been updated to the same value
   AND d.city LIKE 'p% or d.city LIKE 's%'

Edit: columns wrong way around.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #645886

sudhirbharti-565093

Posted Thursday, January 29, 2009 8:04 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 26, 2012 10:39 AM
Points: 19, Visits: 129

can you do for only one records where i can implement the like statement.

Just modify my trigger for the like statement, because my requirement is to update one record or 2 record per day.

Post #645953

Max-146500

Posted Thursday, January 29, 2009 8:12 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 8:35 AM
Points: 271, Visits: 314

GilaMonster (1/29/2009)

...So then the trigger would just looks like this

 WHERE i,city != d,city -- don't catch cases where the city has been updated to the same value
   AND d.city LIKE 'p% or d.city LIKE 's%'

...

Just nab the WHERE clause dude.

Max

Post #645965

Michael Earl-395764

Posted Thursday, January 29, 2009 8:28 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078

GilaMonster (1/29/2009)

IF UPDATE (city)
 insert into PendingEmails (UserID, OldCity, NewCity)
 SELECT i.userid, d.city, i.city FROM inserted inner join deleted ON ... (put the appropriate join based on the PK of the table)
 WHERE i,city != d,city -- don't catch cases where the city has been updated to the same value
   AND d.city LIKE 'p% or d.city LIKE 's%'

Edit: columns wrong way around.

I am a lot less afraid of sending an email from a trigger with sp_send_dbmail. As long as the mail message does not include an attached query result, sp_send_dbmail is primarily adding a record into a service broker queue and the queue processor is sending the email.

Post #645989

« Prev Topic | Next Topic »

Permissions