Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

update trigger Expand / Collapse
Author
Message
Posted Thursday, January 29, 2009 6:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, January 29, 2009 6:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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
Posted Thursday, January 29, 2009 7:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, January 29, 2009 7:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:55 PM
Points: 41,570, Visits: 34,495
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
Posted Thursday, January 29, 2009 8:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Thursday, January 29, 2009 8:12 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 2:27 AM
Points: 280, Visits: 353
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
Posted Thursday, January 29, 2009 8:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #645989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse