Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
Thanks
Every table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more.
|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
ThanksEvery table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
David Portas
SQL Server MVP
--|||If you would like to have a tool to create the tables, generate the triggers
, and manage the audit trail, consider WT6 - one of the SQL Server Tools app
lications...
http://www.sqlservertools.us
If it's compatible with your database architecture it will do what you have
indicated, as well as a lot more.|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>sql

Creating Tables for Auditing

I am working on a DB which requires auditing on certain tables.
I have decided the best way to do this is to replicate some tables (the ones
I require auditing on) and use triggers to insert the original records into
the audit tables when a change is made to the original record.
When i create my audit tables, is there any need to have primary keys,
relationshops, and indexes which also mirror those of my normal tables or
would you say these are unecessary and simple 'basic' tables are all that is
required?
I personally think that basic tables to accept data and give it when
required are all that is needed, but this is the first time I have attempted
anything like this so I wanted some advice from some more knowledgeable
people.
ThanksEvery table should have a Primary Key but you may not want to declare
Foreign Keys on your audit tables if you want to keep rows for which the
parent values may have been deleted. On the other hand if you want to
capture changes to the parent tables as well then your Audit table keys can
mirror your live table keys.
Always declare foreign keys if you can, if only because having the RI in
place will make it simpler to write queries to analyse your audit data.
Here's an article that shows some techniques for capturing changes.
http://www.aspfaq.com/show.asp?id=2448
--
David Portas
SQL Server MVP
--|||If you would like to have a tool to create the tables, generate the triggers, and manage the audit trail, consider WT6 - one of the SQL Server Tools applications..
http://www.sqlservertools.u
If it's compatible with your database architecture it will do what you have indicated, as well as a lot more|||Hi
As you will (probably) have before and after images in these tables and
exact replication of the key is not possible because of duplicate rows. To
make them unique then additional column(s) need adding to make it unique.
You may still want to index on the original tables primary key columns if it
improves the performance of your reporting system.
The tables will be appended too often therefore you are going to slow the
inserts down with more indexes etc.. This may affect the overall performance
of you application. If you are wanting comprehensive reporting then you may
want a compromise on insert speed or alternatively process the audit data
into a more indexed set of tables.
There are third party tools that can do this including:
http://www.lumigent.com/Products/
HTH
John
"Keith" <@..> wrote in message news:ujMmepgTEHA.3660@.tk2msftngp13.phx.gbl...
> I am working on a DB which requires auditing on certain tables.
> I have decided the best way to do this is to replicate some tables (the
ones
> I require auditing on) and use triggers to insert the original records
into
> the audit tables when a change is made to the original record.
> When i create my audit tables, is there any need to have primary keys,
> relationshops, and indexes which also mirror those of my normal tables or
> would you say these are unecessary and simple 'basic' tables are all that
is
> required?
> I personally think that basic tables to accept data and give it when
> required are all that is needed, but this is the first time I have
attempted
> anything like this so I wanted some advice from some more knowledgeable
> people.
> Thanks
>

creating tables

Wondering if I while I create a table I can choose certain values a field can have?
Ex.
CREATE TABLE test
(
Subject_code char(20) NOT NULL,
Student_id char(20) NOT NULL,
Result GOOD or BAD or SUPERGOOD (this is just an example)
)

If a field can have 2 values I can use bit (boolean). But is there a way to do this with several values?Normally you would use a check constraint. Like this:
CREATE TABLE dbo.ExampleTable (
EntryID int NOT NULL IDENTITY(1,1),
Result varchar(32) NOT NULL
CONSTRAINT ExampleTable_Result_CK CHECK (
Result IN ('GOOD', 'BAD', 'SUPERGOOD'))
)

INSERT INTO ExampleTable (Result) VALUES ('GOOD')
INSERT INTO ExampleTable (Result) VALUES ('BAD')
INSERT INTO ExampleTable (Result) VALUES ('UGLY')

SELECT * FROM ExampleTable
Originally posted by buggirl
Wondering if I while I create a table I can choose certain values a field can have?
Ex.
CREATE TABLE test
(
Subject_code char(20) NOT NULL,
Student_id char(20) NOT NULL,
Result GOOD or BAD or SUPERGOOD (this is just an example)
)

If a field can have 2 values I can use bit (boolean). But is there a way to do this with several values?sql

Creating SQL Server Stored Procedures in Deployment

Hey guys,

I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?

However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code.

I would like to create the stored procedure inside that catch block. I just don't know how.

The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.

With the help of the executenonquery u may create the stored procedures which is not the best way. stored procedures,functions or triggers creation means generally people will implement in database not through front end. Not only in this case u can perform ddl commands through executenonquery but max no one will prefer.

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.