update trigger - SQL Server Forums

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)

update trigger

Author

Topic

sudhirbharti
Starting Member

16 Posts

Posted - 2009-01-29 : 08:59:36

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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 09:03:42

First of all, your code will only handle one updated record.
What if 5, 10 or 200 records are updated?

E 12°55'05.63"
N 56°04'39.26"

sudhirbharti
Starting Member

16 Posts

Posted - 2009-01-29 : 09:05:28

Right now I only required for one row at a time, but if you solve for the multiple row, then that would be great for me

quote:
Originally posted by Peso

First of all, your code will only handle one updated record.
What if 5, 10 or 200 records are updated?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 09:11:22

something like this:-


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) 
declare @ID int

select @ID= MIN(PK)
FROM INSERTED
AND i.City LIKE '%p%' 
or i.city like '%s%'

WHILE @ID IS NOT NULL
BEGIN
SELECT @UserID = userid,
@OldCity= d.City,
@NewCity = i.City
FROM deleted d 
JOIN inserted i
ON i.PK=d.PK
WHERE PK=@ID




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'

select @ID= MIN(PK)
FROM INSERTED
WHERE PK>@ID
AND i.City LIKE '%p%' 
or i.city like '%s%'
END
END
GO

----------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 09:36:38

Something like this (send all changes in one mail, and not one mail per change)

CREATE TRIGGER	dbo.trgSendMail_Update
ON		UserInfo
AFTER		UPDATE
AS

DECLARE	@SQL NVARCHAR(MAX)

IF UPDATE(City) AND EXISTS (SELECT * FROM inserted WHERE City LIKE '%p%' OR City LIKE '%s%')
	BEGIN
		SET @SQL =	'
				SELECT		''User ID='' + QUOTENAME(i.UserID, '''''''') + '' has been updated. Previous name is '' + QUOTENAME(d.City, '''''''') + '' and the new name is '' + QUOTENAME(i.City, '''''''') + ''.'' AS Line
				FROM		inserted AS i
				INNER JOIN	deleted AS d ON d.UserID = i.UserID
				'

		EXEC	msdb.dbo.sp_send_dbmail	@profile_name = 'XYZManagement',
						@recipients = 'sbharti_22k1@rediffmail.com;sudhirbhart22i@gmail.com',
						@subject = 'User Information Changed',
						@query = @SQL,
						@importance = 'normal',
						@execute_query_database = 'MyDB',
						@query_result_header = 0,
						@query_result_width = 160
	END
GO

However I strongly advice against having an email sent through a trigger.

E 12°55'05.63"
N 56°04'39.26"

sudhirbharti
Starting Member

16 Posts

Posted - 2009-01-29 : 10:04:29

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.

quote:

Originally posted by visakh16

something like this:-


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) 
declare @ID int

select @ID= MIN(PK)
FROM INSERTED
AND i.City LIKE '%p%' 
or i.city like '%s%'

WHILE @ID IS NOT NULL
BEGIN
SELECT @UserID = userid,
@OldCity= d.City,
@NewCity = i.City
FROM deleted d 
JOIN inserted i
ON i.PK=d.PK
WHERE PK=@ID




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'

select @ID= MIN(PK)
FROM INSERTED
WHERE PK>@ID
AND i.City LIKE '%p%' 
or i.city like '%s%'
END
END
GO

----------------

sudhirbharti
Starting Member

16 Posts

Posted - 2009-01-29 : 10:05:11

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.

quote:

Originally posted by Peso

Something like this (send all changes in one mail, and not one mail per change)

CREATE TRIGGER	dbo.trgSendMail_Update
ON		UserInfo
AFTER		UPDATE
AS

DECLARE	@SQL NVARCHAR(MAX)

IF UPDATE(City) AND EXISTS (SELECT * FROM inserted WHERE City LIKE '%p%' OR City LIKE '%s%')
	BEGIN
		SET @SQL =	'
				SELECT		''User ID='' + QUOTENAME(i.UserID, '''''''') + '' has been updated. Previous name is '' + QUOTENAME(d.City, '''''''') + '' and the new name is '' + QUOTENAME(i.City, '''''''') + ''.'' AS Line
				FROM		inserted AS i
				INNER JOIN	deleted AS d ON d.UserID = i.UserID
				'

		EXEC	msdb.dbo.sp_send_dbmail	@profile_name = 'XYZManagement',
						@recipients = 'sbharti_22k1@rediffmail.com;sudhirbhart22i@gmail.com',
						@subject = 'User Information Changed',
						@query = @SQL,
						@importance = 'normal',
						@execute_query_database = 'MyDB',
						@query_result_header = 0,
						@query_result_width = 160
	END
GO

However I strongly advice against having an email sent through a trigger.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-29 : 10:17:35

it will still work for 1 or 2 records updated

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 10:18:27

The trigger I posted will work for one record only also.
Or two records, or 500 records.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 10:31:48

[code]CREATE TRIGGER dbo.trgSendMail_Update
ON UserInfo
AFTER UPDATE
AS

DECLARE @SQL NVARCHAR(MAX)

IF UPDATE(City) AND EXISTS (SELECT * FROM inserted WHERE City LIKE '%p%' OR City LIKE '%s%')
BEGIN
SET @SQL = '
SELECT ''User ID='' + QUOTENAME(i.UserID, '''''''') + '' has been updated. Previous name is '' + QUOTENAME(d.City, '''''''') + '' and the new name is '' + QUOTENAME(i.City, '''''''') + ''.'' AS Line
FROM inserted AS i
INNER JOIN deleted AS d ON d.UserID = i.UserID
WHERE i.City LIKE ''%[ps]%''
'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'XYZManagement',
@recipients = 'sbharti_22k1@rediffmail.com;sudhirbhart22i@gmail.com',
@subject = 'User Information Changed',
@query = @SQL,
@importance = 'normal',
@execute_query_database = 'MyDB',
@query_result_header = 0,
@query_result_width = 160
END
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources