getting incremental data

Question

getting incremental data

shakti-236987

SSC-Addicted

Points: 438
More actions
December 28, 2005 at 8:24 am

#166950

Dear All,
I have a database which is having more than 100 tables and all the tables are almost updated daily from my application which is on VB. My database is in SQL 2000.
Now what i want is i have to download the incremental data in the text file. Incremental data means i need all the rows created / modified today.
I have one option by which i can create common field and common trigger in each table by which i will update the field whenever rows are inserted/updated in that table. But it seems very cumbersome to create a field and trigger in each table.
Any help in this matter is highly appreciated.
Thanks in Advance.
Shakti

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76001 More actions · Answer 1

I can't see a better way around the need to have a field per table.

If your database design allows there to be a "common properties" table then your UpdatedDate field could go in that table but that still doesn't get around the fact that every single add/update routine needs to update the table.

I would not use a trigger to update the tables because the triggers can be very CPU intensive. I don't know why, they just are.

I would set the UpdatedDate field to have a default of GETDATE() so you only have to worry about the UPDATEs.

LinkedIn Profile

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

You could see if Red Gate's Data Compare or the ApexSQL tool could give you a flat file of data, but it would be by tables.

I like David's advice if you are going to roll your own.

shakti-236987 SSC-Addicted Points: 438 More actions · Answer 3

i agreed by the suggestion given by David. I also have thought about that. But what about Updates. i also need rows that have been updated.

anton Hell Grasshopper Points: 17 More actions · Answer 4

I would still use a trigger: I am using a Audit trigger on all my tables which is generic (see below): Therefor its easy to deploy.

All the Trigger does calles the SYSTEM_Audit Procedure with the Process ID and some other information. System_Audit in turn is the central place where the UPDATED_COLUMNS() is checked and logs are writen depending if the column in question is listed in the table SYS_Objects which has a flag AuditChanges.

I have added another level of abstraction, where SYSTEM_Audit_RegisterObject Procedure is called (not listed here) which checks if the column in question is existing in the tbl_SYS_Objects. If not it inserts it and returns an Audit = 0 , if yes it returns the AuditChange Field (bit field)

Depending on that a log is written or not.

Does that help?

--- Sample Audit Trigger---

CREATETRIGGER _Audit ON

for INSERT, UPDATE, DELETE

AS

declare @GUIDas uniqueidentifier

declare @DeletedGUIDas UniqueIdentifier

SET NOCOUNT On

select @DeletedGUID = GUID from Deleted

select @GUID = GUID from inserted

if @GUID is null and @DeletedGUID is not null

begin

EXEC system_audit @GUID, @@ProcID, 'DELETE'

end else if @GUID is not Null and @DeletedGUID is null

begin

EXEC system_audit @GUID, @@ProcID, 'INSERT'

end else if @GUID is Not Null and @DeletedGUID is not null

begin

EXEC system_audit @GUID, @@ProcID, 'UPDATE'

end

--- Sample Audit Trigger end ---

--- PROCEDURE SYSTEM_Audit---

ALTER PROCEDURE SYSTEM_Audit

@GUIDuniqueidentifier,

@ProcID int,

@UpdateTypevarchar(100)

AS

declare @intCountColumn int,

@n int,

@tblName sysname,

@TblIDint,

@ColNamesysname,

@datavarchar(100),

@sqlvarchar(4000),

@Auditbit

/****************************************************************************************

This Procedure can be called from any Trigger

to audit the table changes

*****************************************************************************************/

-- First determine if an audit should happen

select@Audit = AuditOn

fromtblSys

if @Audit = 0 goto EndProcedure

-- Get the table name in question

select @tblname = object_name(parent_obj) from sysobjects where name = object_name(@ProcID)

--print @ProcID

--print @Tblname

-- Obtain the table object ID

set @tblID = object_ID(@TblName)

-- Check if Auditing is enabled for this Object

exec @Audit = System_Audit_RegisterObject @TblName, 'TABLE', @TblID, 0

--print 'Audit is ' + convert(varchar(1), @Audit)

-- Log Inserts and Deletes on every table regardsless of audit Flag

if @UpdateType = 'INSERT' begin

insert into tbl_Sys_auditLog

(DB_user, EventType, TableName, TableID, Data, RecordGUID)

values (user, @UpdateType, @TblName, @TblID, '>>> INSERT RECORD ' + convert(varchar(40), @GUID), @GUID)

end else if @UpdateType = 'DELETE' begin

insert into tbl_Sys_auditLog

(DB_user, EventType, TableName, TableID, Data, RecordGUID)

values (user, @UpdateType, @TblName, object_ID(@TblName), '<< 0

begin

-- Get the Bitmask Portion

set @n = ceiling(@intCountColumn / 8.0)

--Print 'Checking columne ' + @tblname +'.' + COL_NAME(object_id(@tblname), @intCountColumn)

-- Check if column was updated

if substring(COLUMNS_UPDATED(), @n, 1) & power(2, (@intCountColumn-8*(@n-1)-1)) > 0

begin

set @ColName = COL_NAME(object_id(@tblname), @intCountColumn)

-- check if columne needs to be audited

exec @Audit = System_Audit_RegisterObject @ColName, 'COLUMN', @TblID, 0

--Print ' Data changed in ' + @tblname +'.' + @ColName

-- If audit is on then audit

if @audit = 1 exec SYSTEM_Audit_WriteLog @GUID, @tblName, @ColName

end

Set @intCountColumn = @intCountColumn - 1

End

--- END of PROCEDURE SYSTEM_Audit---

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Why build on David's suggestion not devote 2 columns instead of 1. The first column is a creation date/tim, the second column is a modification date/time. (it's something we did 2+ decades ago in the mainframe world ... back then we also added creating user and modifying user too !)

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."