update trigger

Question

update trigger

sudhirbharti-565093

Mr or Mrs. 500

Points: 539
More actions
January 29, 2009 at 6:53 am

#196664

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 @user-id varchar(10)
declare @body varchar(2000)
declare @OldCity varchar(10)
declare @NewCity varchar(10)
SELECT @user-id = 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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

sudhirbharti (1/29/2009)
SELECT @user-id = 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, MVP, M.Sc (Comp Sci)
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

sudhirbharti-565093 Mr or Mrs. 500 Points: 539 More actions · Answer 2

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

Any ideas?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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, MVP, M.Sc (Comp Sci)
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

sudhirbharti-565093 Mr or Mrs. 500 Points: 539 More actions · Answer 4

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.

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 5

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

GilaMonster (1/29/2009)
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.

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.