How to detect if column data changed and know prev. and new value

I have a need to insert rows into an Audit type table when values
change in certain fields in a table. I thought I could do this via a
trigger. However, on requirement is to include in the audit both the
old and new value.

Is there a "simple" way to do this? I know I could query the table
before the update and compare to what the new value is and react
accordingly.

Just wondering if there is something nifty in Sql Server that I am
missing that could help me with this.

Thanks in advance for your help.

BillHi

Check out CREATE TRIGGGER in Books Online or at
http://msdn.microsoft.com/library/d...asp?frame=true

In particular the COLUMNS_UPDATED example of the IF UPDATE clause.

John

"Bill Tepe" <billtepe@.mssonline.net> wrote in message
news:7364847c.0309060600.7023b89a@.posting.google.c om...
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.
> Is there a "simple" way to do this? I know I could query the table
> before the update and compare to what the new value is and react
> accordingly.
> Just wondering if there is something nifty in Sql Server that I am
> missing that could help me with this.
> Thanks in advance for your help.
> Bill|||[posted and mailed, please reply in news]

Bill Tepe (billtepe@.mssonline.net) writes:
> I have a need to insert rows into an Audit type table when values
> change in certain fields in a table. I thought I could do this via a
> trigger. However, on requirement is to include in the audit both the
> old and new value.

In a trigger you can retrieve the new value in the "inserted" table
and the old value in the "deleted" tables. These tables are virtual
and are accessible only in the trigger.

Beware that a trigger in SQL Server fires once per statement, not once
per row as in some other products. Thus, the tables can old many rows.

You should also be aware of access to these tables when they contain
many rows can be slow. Therefore it is often good idea to start a trigger
with:

select * INTO #tblname_inserted FROM inserted
select * INTO #tblname_deleted FROM deleted

Since you are into auditing... If you are doing this on any large
scalce, you should probably consider third-party solutions rather
than reinventing the wheel. www.redmatrix.com has a product SQLAudit,
which I have no experience of myself.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||If you need to do this at more of an enterprise level, you might look
into Lumigent's Entegra (haven't used it but buying it next year :))

http://lumigent.com/products/entegra/entegra.htm

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

how to detect if an alias type is existed or not before create type?

how to detect if an alias type is existed or not before create type?Hello, Frank
Look into the sys.types catalog view. For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms188021.aspx
Razvan

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?
Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>
|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel[vbcol=seagreen]
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema
> ?
>
|||Why not make "Education Certificate" a dimension all of its own and then have a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

how to design string type datawarehouse?

if the fact table have some string type measure,how to design start schema ?Could you be more specific as to what you're trying to do? Why do you feel
that a string datatype measure would pose a problem?
"x" <xiaopeng@.creditbeijing.com.cn> wrote in message
news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
> if the fact table have some string type measure,how to design start schema
?
>|||For example,the fact table record some education information: somebody in
somewhere at sometime get some education level certification.
how to design this star schema?
so no measure or only string type column(can't be aggregation) ,How to
tuning the database for ad hoc query?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> д?
news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Could you be more specific as to what you're trying to do? Why do you
feel
> that a string datatype measure would pose a problem?
>
> "x" <xiaopeng@.creditbeijing.com.cn> wrote in message
> news:%23vy5$UMaEHA.2944@.TK2MSFTNGP11.phx.gbl...
schema[vbcol=seagreen]
> ?
>|||Why not make "Education Certificate" a dimension all of its own and then hav
e a simple measure "Count".
Regards
Jamie
"x" wrote:

> For example,the fact table record some education information: somebody in
> somewhere at sometime get some education level certification.
> how to design this star schema?
> so no measure or only string type column(can't be aggregation) ,How to
> tuning the database for ad hoc query?
>
>
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> D′è????￠
> news:e9LU7qQaEHA.1248@.TK2MSFTNGP11.phx.gbl...
> feel
> schema
>
>

how to deploy a script

Hello,

What i'm looking for is a way to write a script which would change few data type fields in a specific table, in a specific database. Like i want to change it from char(4) to varchar(15). And then deploy this script, so that when he executes it, changes are reflected in his database. I may use query analyzer to excute i think. Plz also tell me how to write that script.(not sure if its called script).

Thank You.

Use the ALTER TABLE ALTER COLUMN command. Here is an example:

Code Snippet

ALTER TABLE MyTable ALTER COLUMN Col1 VarChar(100)

where MyTable is the table you're updating and Col1 is the column that you want to change its type.

I hope this answers your question.

Best regards,

Sami Samir

|||

if you don't need to alter your source database you can use this approach,

You can get all the table scripts from "SQL Server - Enterprise Manager" using Generate SQL Script.

After that you can find the Char(7) datatype and replace with varchar(15). (you can simply use any text editors Find & Replace)

|||

ya, i used following,

ALTER TABLE GRNs
ALTER COLUMN GRNNo VARCHAR(15) NOT NULL

i was just wondering if it can be conditional, like what's use of running it if its already varchar.

is their anything like if else?

|||

Try the following script:

Code Snippet

Declare @.Type as Int

Declare @.Length as Int

Select @.Type = System_Type_ID, @.Length = Max_Length

From sys.objects Inner Join sys.columns On sys.objects.Object_ID = sys.columns.Object_ID

Where sys.objects.Name = GRNs And sys.columns.Name = GRNNo

--Check that the column actually exists

If @.@.RowCount <> 0

Begin

--Check if it is already Varchar (ID is 167) and length is already 15

If @.Type <> 167 Or @.Length <> 15

Alter Table GRNs Alter Column GRNNo Varchar(15)

End

I hope this helps.

Best regards,

Sami Samir