![SoftTree Technologies](templates/GreenTech/images/logo.gif) |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
raviraj
Joined: 31 Aug 2011 Posts: 16 Country: India |
|
Auditing Alter Table |
|
Hi,
As per your replies for the post titled "Audited Table Schema Column Addition Change" id I alter table to add one column and after that If I want to include the same column into the audit table then the tool will drop the existing table and recreate the table with new columns. So all the previous audited data would be lost? Is their any way to save the previous data.
How to use the API's? Is their any trial version for the same?
|
|
Wed Aug 31, 2011 12:42 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
In recent versions before dropping the old table, DB Audit Management Console displays a prompt offering to move the old data to the new audit table so that you can keep the old audit trail data and new one in one place. That prompt is not displayed in case the structures of the old and new tables cannot be automatically matched and it doesn't know how to move the data from the old table. In such case, you should get a prompt offering to export data to an external file so that you can import it manually later
|
|
Wed Aug 31, 2011 1:17 pm |
|
![](templates/GreenTech/images/spacer.gif) |
raviraj
Joined: 31 Aug 2011 Posts: 16 Country: India |
|
|
|
Hi,
In the reply I see that there is an option to migrate data from the old to the new table but there are some limitations. Exactly what are the limitations for "structures of the old and new tables cannot be automatically matched". What is the matching criteria?
Thanks,
|
|
Tue Sep 06, 2011 12:36 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Hi,
It's quite simple. If you add new columns to the audited table, the old data can be migrated automatically, because the table structure is obviously extended. If you rename columns, change data types, remove columns from the audited table, the data cannot be migrated automatically, you would need to do that manually if you want to preserve it.
Hope that helps
|
|
Wed Sep 07, 2011 9:32 am |
|
![](templates/GreenTech/images/spacer.gif) |
raviraj
Joined: 31 Aug 2011 Posts: 16 Country: India |
|
|
|
Hi,
I tried what your mentioned in the previous reply. But I am getting the exact opposite result of what you have said. If I insert new column to the table and from the tool select it for auditing then it prompts that the audit table will be dropped and recreated and if I rename any column then it prompts that do you want to save the previous audit log(Yes/No). Is it correct?
Thanks
|
|
Thu Sep 08, 2011 3:16 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Hi,
I'm unable to reproduce it. Are you adding new column to the end of the table or somewhere in a middle?
Can you provide DDL for your table so we can see what else might be causing that behavior?
Thanks
|
|
Fri Sep 09, 2011 12:23 pm |
|
![](templates/GreenTech/images/spacer.gif) |
raviraj
Joined: 31 Aug 2011 Posts: 16 Country: India |
|
|
|
Hi,
I am adding new column to the end of the table.
The DDL is,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customer](
[id] [int] IDENTITY(1,1) NOT NULL,
[firstName] [varchar](25) NULL,
[lastName] [varchar](25) NULL,
[middleName] [varchar](1) NULL,
[CustomerId] [varchar](25) NULL,
[address1] [varchar](250) NULL,
[address2] [varchar](25) NULL,
[city] [varchar](25) NULL,
[state] [varchar](25) NULL,
[zipCode] [varchar](25) NULL,
[eMail] [varchar](25) NULL,
[dob] [datetime] NULL,
[gender] [char](1) NULL,
[height] [varchar](25) NULL,
[weight] [varchar](25) NULL,
[country] [varchar](25) NULL,
[primaryPhone] [varchar](25) NULL,
[secondaryPhone] [varchar](25) NULL,
[type] [varchar](50) NULL,
[pgid] [int] NULL,
[auditCheck] [int] NULL,
[audit] [varchar](50) NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Thanks
|
|
Tue Sep 13, 2011 8:30 am |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7854
|
|
|
|
Thank you. Give me some time to test it in my test audit database. From the DDL I assume this is a SQL Server 2005 or 2008 database.
|
|
Tue Sep 13, 2011 9:42 am |
|
![](templates/GreenTech/images/spacer.gif) |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|