sql trigger on specific data with column

Question

Hi

I am trying to create a trigger on a database called dbo.DHCPReport, which has a column called "returneduserclass nvarChar(128)". I want to have the trigger send a email when the returneduserclass column is populated with the following "NACDeny". the trigger I have is below, currently all this does is email when any "insert" occurs

create TRIGGER [dbo].[email_IT8] ON [ReportStore].[dbo].[DHCPReport] FOR update AS DECLARE @returneduserclass nvarChar(128) if update(returneduserclass) SELECT @returneduserclass='nacdeny'

Where am I going wrong ?

Answer 1

First off, are you trying to send this email when a record is INSERTED or when a record is UPDATED? They are 2 different things...

IF after an INSERT then you can capture the new row and interrogate as you would any variable: REFERENCING NEW AS N
IF N.Returneduserclass = 'nacdeny' then
do stuff
End if

Answer 2

Hi Lesley

SQL triggers are very, very new to me, so apologies if i get any terminology wrong! I'm after the data when its inserted. If that is the case where abouts in the trigger does the "REFERENCING NEW AS N IF N.Returneduserclass = 'nacdeny'" get inserted ?

Is any of sql existing trigger correct ? I did replace the first "update" with insert just to turn the trigger off for testing etc. Also, should the second "update" be an "insert" ? If i try that it comes back with errors.

Thank you