asked on

Audit Trigger

I am looking to audit inserts,updattes and deletes on my various tables.

What I am looking to do is when a record in the db is :

- inserted - capture the date, user that inserted
- updated - capture the date,user that updated
- deleted - capture the date,user that deleted the record but I am just going to label it as deleted and prevent the delete.

here is an example of one of the tables i am looking to audit.

CREATE TABLE [dbo].[SENTRY] (
[ID] [varchar] (20) NOT NULL ,
[ProfileManager] [varchar] (50) NOT NULL ,
[SubSource] [varchar] (50) NOT NULL ,
[SentrySeverity] [varchar] (8) NOT NULL ,
[MonStatus] [varchar] (8) NOT NULL ,
[Schedule] [varchar] (250) NOT NULL ,
[Monitor] [varchar] (250) NOT NULL ,
[Keyphrase] [varchar] (100) NOT NULL ,
[Probearg] [varchar] (250) NOT NULL ,
[Condition] [varchar] (100) NOT NULL ,
[Interval] [varchar] (50) NOT NULL ,
[TecSeverity] [varchar] (8) NOT NULL ,
[Email] [varchar] (1024) NOT NULL ,
[Logfile] [varchar] (250) NOT NULL ,
[Icon] [varchar] (100) NOT NULL ,
[Notice] [varchar] (100) NOT NULL ,
[Popup] [varchar] (100) NOT NULL ,
[RunProgram] [varchar] (250) NOT NULL ,
[OncallLabel] [varchar] (100) NULL ,
[DocLabel] [varchar] (512) NULL ,
[Product] [varchar] (50) NULL ,
[SubProduct] [varchar] (50) NULL ,
[Comments] [varchar] (1024) NULL ,
[TsengNotes] [varchar] (1024) NULL ,
[DescriptionLabel] [varchar] (250) NULL ,
[SecOncallLabel] [varchar] (100) NULL ,
[TerOncallLabel] [varchar] (100) NULL ,
[CustomScript] [varchar] (250) NULL ,
[CustomRelationship] [varchar] (50) NULL,
[RowState] [varchar] 20 NULL,
[RowUpdated] [varchar] 50 NULL,
[RowUpdatedBy] [varchar] 20 NULL
) ON [PRIMARY]
GO

I have a column in each table called RowState,RowUpdated,RowUpdatedBy.

I am looking to record the state of that row (INSERTED,DELETED,UPDATED) in the table's RowState. The GETDATE in the RowUpdated and the RowUpdatedBy with the username.

My trouble has been figuring out what is being inserted,updated and deleting and ensuring that those rows are updated correctly. I want the abillity to determine if ANY of the records where updated in the row.

kkelley6

ASKER

Actually, if it is easier/better to consolidate the audit into it's own table ...I am open to that. due to the number of tables I have I would just need to capture the before and after values.

nigelrivett

See
www.nigelrivett.com
Audit trail trigger

This will table a table and create an audit trail trigger for it.

It will audit to
create table Audit (TableName varchar(128), FieldName varchar(128), OldValue varchar(1000), NewValue varchar(1000))
go

And put in the table, field names and before and after values of any fields changed.
For your requirement you just need to add getdate() and system_user to the table.

kkelley6

ASKER

nigelrivett, this seems to be the way to go. I added the trigger to the table and with every modification I get the 'no pk' error message. but tthe table does have one.

CREATE TABLE [dbo].[SENTRY] (
[ID] [varchar] (20) NOT NULL ,
[ProfileManager] [varchar] (50) NOT NULL ,
[SubSource] [varchar] (50) NOT NULL ,
[SentrySeverity] [varchar] (8) NOT NULL ,
[MonStatus] [varchar] (8) NOT NULL ,
[Schedule] [varchar] (250) NOT NULL ,
[Monitor] [varchar] (250) NOT NULL ,
[Keyphrase] [varchar] (100) NOT NULL ,
[Probearg] [varchar] (250) NOT NULL ,
[Condition] [varchar] (100) NOT NULL ,
[Interval] [varchar] (50) NOT NULL ,
[TecSeverity] [varchar] (8) NOT NULL ,
[Email] [varchar] (1024) NOT NULL ,
[Logfile] [varchar] (250) NOT NULL ,
[Icon] [varchar] (100) NOT NULL ,
[Notice] [varchar] (100) NOT NULL ,
[Popup] [varchar] (100) NOT NULL ,
[RunProgram] [varchar] (250) NOT NULL ,
[OncallLabel] [varchar] (100) NULL ,
[DocLabel] [varchar] (512) NULL ,
[Product] [varchar] (50) NULL ,
[SubProduct] [varchar] (50) NULL ,
[Comments] [varchar] (1024) NULL ,
[TsengNotes] [varchar] (1024) NULL ,
[DescriptionLabel] [varchar] (250) NULL ,
[SecOncallLabel] [varchar] (100) NULL ,
[TerOncallLabel] [varchar] (100) NULL ,
[CustomScript] [varchar] (250) NULL ,
[CustomRelationship] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[SENTRY] WITH NOCHECK ADD
CONSTRAINT [PK_SENTRY1] PRIMARY KEY NONCLUSTERED
(
[ID],
[ProfileManager],
[SubSource],
[SentrySeverity]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[SENTRY] ADD
CONSTRAINT [FK_SENTRY_DESCRIPTION] FOREIGN KEY
(
[DescriptionLabel]
) REFERENCES [dbo].[DESCRIPTION] (
[DescriptionLabel]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_SENTRY_DOC] FOREIGN KEY
(
[DocLabel]
) REFERENCES [dbo].[DOC] (
[DocLabel]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_SENTRY_ONCALL] FOREIGN KEY
(
[OncallLabel]
) REFERENCES [dbo].[ONCALL] (
[OncallLabel]
) NOT FOR REPLICATION
GO

ASKER CERTIFIED SOLUTION

nigelrivett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial