sql server mseq: Using Variable as column Name in select query -- sql server mseq

Using Variable as column Name in select query

LittleJonny
10/23/2003 11:20:38 AM

sql server mseq: What am I missing here?

Declare @OldValue varchar(50), @FieldName varchar(50)
select @FieldName = 'email'
Select @OldValue = (Select @FieldName from Contacts where
contactid = 32)
print @OldValue

OldValue always returs as "email" instead
of "emaaddress@somwhere.com"

I am writing a table audit trigger. Does this sound right?

Get the columns of the tables from the information schema.
Loop through the columns and use Columns_updated()
resultst to see if column has been changed.

Use the @ColumnName to get the old and new from deleted
and inserted (this is where the above problem comes in).

Stick the information in the Audit Table and alert the
proper authorities.

Thanks

Re: Using Variable as column Name in select query

Ray Higdon
10/23/2003 10:32:42 PM

How about this

create trigger foo on blah
for update
as
if updated (email)
insert into audittable (oldemail, newemail)
select d.email, a.email from contacts a join deleted d on
a.contactid = d.contactid where a.contactemail != d.contactemail

or even better, capture who and when this occurred

create trigger foo on blah
for update
as
if updated (email)
insert into audittable (oldemail, newemail,baduser,occurencedate)
select d.email, a.email,suser_sname(),current_timestamp from contacts a join
deleted d on
a.contactid = d.contactid where a.contactemail != d.contactemail

Think that will do what you need, you might throw a trigger on your
audittable to send an email to the appropriate authorities using xp_sendmail

HTH

--
Ray Higdon MCSE, MCDBA, CCNA
---
[quoted text, click to view]