February 10, 2003 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
February 24, 2003 at 9:41 pm
In our web based app operating (of course) in a statless env, we have three columns for each table:
update_count, int
last_updated_by, varchar(50)
last_updated_on, datetime
each table (where appropriate), has an equivalent *_Audit version, and a single reference data table recording the audit type, being insert/delete/update.
We wrote a small SQLDMO app to generate the tables, triggers etc for auditing, an example trigger is at the end of this post.
The real issue here is blob fields, and using identity cols (use SCOPE_IDENTITY()!) if you require a seqeuence field in the audit table. The other fields mentioned above are programatically managed via the app as we dont use windows authentication.
CREATE TRIGGER dbo.Access_Type_Audit_Trigger ON dbo.Access_Type
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@insert_countint,
@delete_countint,
@current_timedatetime
SET NOCOUNT ON
SET @current_time= GETDATE()
SET @insert_count= (SELECT COUNT(*) FROM INSERTED)
SET @delete_count= (SELECT COUNT(*) FROM DELETED)
/* A new record is inserted */
IF @insert_count > 0 AND @delete_count = 0
BEGIN
INSERT INTO CorpSysAud.Access_Type_Audit
(
access_type_code,
access_type_desc,
update_count,
last_updated_on,
last_updated_by,
Audit_Action, Modified_By, Modified_Time
)
SELECT
access_type_code,
access_type_desc,
update_count,
last_updated_on,
last_updated_by,
1, last_updated_by, @current_time
FROM INSERTED
END
/* A record is updated */
IF @insert_count > 0 AND @delete_count > 0
BEGIN
INSERT INTO CorpSysAud.Access_Type_Audit
(
access_type_code,
access_type_desc,
update_count,
last_updated_on,
last_updated_by,
Audit_Action, Modified_By, Modified_Time
)
SELECT
access_type_code,
access_type_desc,
update_count,
last_updated_on,
last_updated_by,
2, last_updated_by, @current_time
FROM INSERTED
END
/* A record is deleted */
IF @insert_count = 0 AND @delete_count > 0
BEGIN
INSERT INTO CorpSysAud.Access_Type_Audit
(
access_type_code,
access_type_desc,
update_count,
last_updated_on,
last_updated_by,
Audit_Action, Modified_By, Modified_Time
)
SELECT
access_type_code,
access_type_desc,
update_count,
last_updated_on,
last_updated_by,
3, last_updated_by, @current_time
FROM DELETED
END
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
February 25, 2003 at 9:23 am
We do auditing on our Remote Users in a replicated environment. So the triggers only fire when the replication process is doing the inserts/updates. This is done in a Sybase Adaptive Server Anywhere environment so the syntax will be a little different in SQL Server.
For all of our Data Entry tables that a remote user can modify data in; we create a copy of the table with audit_ at the beginning with a primary key of an identity column. Then we have triggers for Updates and Inserts that fired. For an update we will insert the old values and the new values of the updated row. This allows us to see the modification history of a record and actually has allowed us to rollback some data modifications that the remote user has performed by accident.
CREATE trigger dba.tr_entity_update before update on
entity
referencing old as old_row new as new_row
for each row
begin
declare RIGHT_NOW DATETIME;
declare MY_USER char(5);
set RIGHT_NOW=GETDATE(*);
if current remote user is not null then
if current publisher = 'devpublisher' then
set MY_USER=current remote user
else
set MY_USER=current publisher
end if;
-- DO AUDITING IF THIS IS THE CONSOLIDATE
if(current publisher = 'devpublisher') then
-- ADD UPDATE AUDITING
insert into audit_master
(rem_user,
upd_time,
table_name)
values(current remote user,
RIGHT_NOW,
'entity');
-- INSERT OLD VALUES
insert into audit_entity
(entity_id,
parent_id,
md_description,
status,
mod_user,
mod_datetime,
type_flag,
nerc_description,
loaded_date,
source_site_id,
data_source)
values(
old_row.entity_id,
old_row.parent_id,
old_row.md_description,
old_row.status,
old_row.mod_user,
old_row.mod_datetime,
old_row.type_flag,
old_row.nerc_description,
old_row.loaded_date,
old_row.source_site_id,
old_row.data_source);
-- INSERT NEW VALUES
insert into audit_entity
(entity_id,
parent_id,
md_description,
status,
mod_user,
mod_datetime,
type_flag,
nerc_description,
loaded_date,
source_site_id,
data_source)
values( new_row.entity_id,
new_row.parent_id,
new_row.md_description,
new_row.status,
new_row.mod_user,
new_row.mod_datetime,
new_row.type_flag,
new_row.nerc_description,
new_row.loaded_date,
new_row.source_site_id,
new_row.data_source)
end if
end if
end
February 25, 2003 at 1:19 pm
Part 2 will look at separate tables.
Interesting thought about the sequence table.
Steve Jones
April 2, 2003 at 9:23 am
Ever consider reviewing Auto-Audit (lockwoodtech.com) and similar products?
-Frank Ramage
-Gold Committee Chairperson
-Cortex User Group (cortexusergroup.com)
Edited by - framage on 04/02/2003 09:24:58 AM
April 2, 2003 at 11:47 am
considered, but backed up with reviews. I hope to tackle it at some point.
Steve Jones
April 4, 2003 at 12:53 pm
I'm implementing the following (and am open for discussion).
I have an action table for assigning a numeric ID to INSERT, UPDATE and DELETE actions. (overkill I suppose)
Every table has 4 tracking columns:
created_dt DateTime NULL CONSTRAINT DF_tablename_created_dt DEFAULT (GETDATE()),
created_nm VarChar(64) NULL CONSTRAINT DF_tablename_created_nm DEFAULT (USER_NAME()),
lastmod_dt DateTime NULL CONSTRAINT DF_tablename_lastmod_dt DEFAULT (GETDATE()),
lastmod_nm VarChar(64) NULL CONSTRAINT DF_tablename_lastmod_nm DEFAULT (USER_NAME())
Each tracking field has it's own default value of GETDATE() and USER_NAME() where appropriate.
Each table has an UPDATE trigger which updates the lastmod_dt and lastmod_nm fields with the appropriate values.
For each (production) table, I have:
• History table - this is an exact replica with 3 additional fields: action_id Int, action_bm VarBinary(128), action_desc VarChar(128) Null (reserved for future use)
• INSERT Trigger - Inserts into the appropriate history table data from the INSERTED virtual table. Also retrievs the action_id via function. Inserts the COLUMNS_UPDATED() value into action_bm.
• UPDATE Trigger - behaves the same as the INSERT trigger. Since I can see what the values were before the update, I INNER JOIN with the UPDATED table instead of the DELETED table.
• DELETE Trigger - similar, but uses the DELETED virtual table.
Finally, I have a function which will convert the Hex (action_bm) into a list of the updated columns.
April 4, 2003 at 1:50 pm
Similar approach to hwat I've done in Part 1 and II. Thanks for sharing
Steve Jones
February 24, 2004 at 10:26 am
Both in this example and in the article's example, the column that stores the 'USER_NAME()' and 'SUSER_SNAME()' results is defined as something other than the return value for these functions (varchar(64) and varchar(80), respectively).
It may be unlikely to cause problems, but perhaps the columns should be defined as nvarchar(256), which is the correct return type for both USER_NAME() and SUSER_SNAME().
TroyK
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply