Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


update trigger


update trigger

Author
Message
sudhirbharti-565093
sudhirbharti-565093
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57105 Visits: 44707
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, 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
sudhirbharti-565093
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 129
thanks I haven't think about that, what i have to do for the multiple row.
Any ideas?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57105 Visits: 44707
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
sudhirbharti-565093
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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.
Max-146500
Max-146500
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 363
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
Michael Earl-395764
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3135 Visits: 23078
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search