How to create a trigger when a data value changes to a stringsubset.

Question

Post reply

How to create a trigger when a data value changes to a stringsubset.

steve.treloar

SSC Enthusiast

Points: 142
More actions
December 7, 2010 at 2:35 pm

#242174

I've got a table that gets updated from several sources.. There's specific value that I want to know when it is changed/written. So, for x.table x.column, I want to know when string '%xxxyx' is written. I'd like a trigger that will send an alert, of the data change to %xxxyx, and the value of that data.

I have tested SQL mail. That works fine from the create profile wizard.

Here's what I have so far. The trigger did create. I can alter the trigger. It just won't send email when the datavalue is changed[/i]. What I don't know, is whether the logic is failing or if the email send is failing. (like I said, SQLmail test worked fine)

--//TriggerName within TableName

CREATE TRIGGER WrongEmailAddress ON dbo.users AFTER insert,update,delete

AS

--//ColumnName

DECLARE @email varchar(50)

--//DataValue

SET @Email = (SELECT email FROM inserted)

--//Test DataValueStringSubset . The % is wildcard for anything prior to the @ sign. The xxxyx is the part that I want to know changed.

IF @Email = '%@xxxyx'

--//True?

BEGIN

DECLARE @msg varchar(500)

--// SET THE Message sent, Recipient list and subject using which SQLmail profile.

SET @msg = 'WrongEmailAddress "' + @Email + '" entered into user email at $' + '.'

EXEC msdb.dbo.sp_send_dbmail @recipients=N'MyEmailAddressHere', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Administrator'

END

GO

Viewing 5 posts - 1 through 4 (of 4 total)

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

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 1

Let's make sure the logic is working as expected first.

Try adding a little debug code just before you send the email. Write out a "got this far" message to a table, then check the table afterwards to see if it worked.

Be sure to include the value of the @email variable when you log the message.

You should also be aware that when you get more than one row returned from a SELECT you aren't sure which value is actually being

stored in the @email variable.

Let me know how it works.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Lowell SSC Guru Points: 323482 More actions · Answer 2

i think the problem is when you are comparing @email. it is looking for an exact match...no wildcards.

i think it should be this:



IF @Email LIKE '%@xxxyx'

are you REALLy testing for xxxyx? i hate pseudocode...what are you really testing for?

try this code outside of your trigger...you can see whether the value is found or not...so then you can incorporate it into your trigger properly:



/*--Results

WrongEmailAddress "bananas@xxxyx" entered into user email at $.

*/

--//ColumnName

DECLARE @email varchar(50)

--//DataValue

SET @Email = 'bananas@xxxyx'

--//Test DataValueStringSubset . The % is wildcard for anything prior to the @ sign. The xxxyx is the part that I want to know changed.

IF @Email LIKE '%@xxxyx'

--//True?

  BEGIN

	DECLARE @msg varchar(500)

    --// SET THE Message sent, Recipient list and subject using which SQLmail profile.

    SET @msg = 'WrongEmailAddress "' + @Email + '" entered into user email at $' + '.'

    PRINT @msg

    --EXEC msdb.dbo.sp_send_dbmail @recipients=N'MyEmailAddressHere', @body= @msg, @subject = 'SQL Server Trigger Mail', @profile_name = 'Administrator'

  END

ELSE

  BEGIN

  PRINT 'Not A match'

  END

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steve.treloar SSC Enthusiast Points: 142 More actions · Answer 3

Thank you for your help!

Now I'm getting two messages/emails for every data change that meets the criteria. Dunno why. I can live with that for now though.

The logic wasn't working.. Printing to screen helped me to figure out the syntax.

This is what I ended up with:

--//TriggerName within TableName

ALTER TRIGGER WrongEmailAddress ON dbo.users AFTER insert,update,delete

AS

--//ColumnName

DECLARE @email varchar(50)

--//DataValue

SET @Email = (SELECT email FROM inserted)

--//Test DataValue . The % is wildcard for anything prior to the @ sign.

IF @Email LIKE '%@xxxyx'

--//True?

BEGIN

--//Do all the messaging stuff.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Bhuvnesh

SSC Guru

Points: 59351

December 8, 2010 at 6:40 am

#1260058

steve.treloar (12/7/2010)

Now I'm getting two messages/emails for every data change that meets the criteria. Dunno why. I can live with that for now though.

Post your complete trigger script

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)