Audit trial System on SQL server

Hi, i want to keep a log history of whatever happens to the records in a table and store in a history table. the table should contain who inserted/deleted/modified what, the data modified, and time of modified. how can i do this.
Below is the structure of my main table.

I am new to SQL server.

USE [Gambis]
GO
/****** Object:  Table [dbo].[Bio_Data]    Script Date: 07/02/2009 22:34:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bio_Data](
	[userid] [bigint] NOT NULL,
	[NIN] [varchar](50) NULL,
	[first_Name] [varchar](25) NULL,
	[middle_Initial] [varchar](25) NULL,
	[last_Name] [varchar](25) NULL,
	[birthPlace] [varchar](50) NULL,
	[birthdatetime] [datetime] NULL,
	[occupation] [varchar](25) NULL,
	[nationality] [varchar](25) NULL,
	[permanent_address] [varchar](50) NULL,
	[address] [varchar](50) NULL,
	[employer_TIN] [bigint] NULL,
	[next_of_kin] [varchar](50) NULL,
	[datetime_created] [datetime] NULL,
	[datetime_modified] [datetime] NULL,
	[startdatetime] [datetime] NULL,
	[end_datetime] [datetime] NULL,
	[photo] [image] NULL,
	[gender] [varchar](15) NULL,
	[Permit_Type] [char](1) NULL,
	[email] [varchar](50) NULL,
	[next_of_kin_tel] [varchar](25) NULL,
	[card_number] [bigint] NULL,
	[sig_id_number] [varchar](50) NULL,
	[entry_point] [varchar](50) NULL,
	[datetime_of_entry] [datetime] NULL,
	[addr_of_next_of_kin] [varchar](75) NULL,
	[marital_status] [varchar](50) NULL,
	[name_of_employer] [varchar](75) NULL,
	[address_of_employer] [varchar](75) NULL,
	[place_of_issue] [varchar](50) NULL,
	[receiptNo] [varchar](50) NULL,
	[scannedFinger] [varchar](50) NULL,
	[fingerprintImage] [image] NULL,
	[Purpose of Entry] [text] NULL,
	[NIN of Approving Officer] [varchar](50) NULL,
	[NIN of Verifying Officer] [varchar](50) NULL,
	[Date Approved] [datetime] NULL,
	[Date Verified] [datetime] NULL,
	[Father's Full Name] [text] NULL,
	[Father's Place of Birth] [text] NULL,
	[Mother's Full Name] [text] NULL,
	[Mother's Place of Birth] [text] NULL,
	[Authenticator's Full Name] [text] NULL,
	[Authenticator's Profession] [text] NULL,
	[Date Authenticated] [datetime] NULL,
	[Document Produce] [varchar](50) NULL,
	[duration] [int] NULL,
	[details and address of properties in The Gambia] [text] NULL,
	[Issued at] [varchar](50) NULL,
	[Date Issued] [datetime] NULL,
	[Passport No] [varchar](50) NULL,
	[Dependant 1 FullName] [text] NULL,
	[dependant 1 Relationship] [text] NULL,
	[Dependant 2 FullName] [text] NULL,
	[Dependant 2 Relationship] [text] NULL,
	[Dependant 3 FullName] [text] NULL,
	[Dependant 3 relationship] [text] NULL,
	[Dependant 4 FullName] [text] NULL,
	[Dependant 4 Relationship] [text] NULL,
	[Dependant 1 age] [int] NULL,
	[Dependant 2 age] [int] NULL,
	[Dependant 3 age] [int] NULL,
	[Dependant 4 age] [int] NULL,
	[Document checked] [varchar](50) NULL,
	[Approval Status] [varchar](50) NULL,
	[Captured] [varchar](50) NULL,
	[Data Entry Completed] [varchar](50) NULL,
	[Handed to Cashier] [varchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

AtourayAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need the history table with the same fields than your original table + the fields you mentioned: who (user)/when (time)/how (insert/udpate/delete)

then, 1 (or 3) plain trigger (s) on the table
CREATE TRIGGER trg_audit_delete_biodata
 ON biodata
 FOR DELETE
AS
  INSERT INTO your_audit_table
   ( who, when, how, userid, NIN, ... etc )
  SELECT suser_name(), getdate(), 'DELETE', userid, NIN, ... etc
    FROM DELETED 
go 

CREATE TRIGGER trg_audit_update_biodata
 ON biodata
 FOR UPDATE
AS
  INSERT INTO your_audit_table
   ( who, when, how, userid, NIN, ... etc )
  SELECT suser_name(), getdate(), 'UPDATE', userid, NIN, ... etc
    FROM INSERTED 
go 
CREATE TRIGGER trg_audit_insert_biodata
 ON biodata
 FOR INSERT
AS
  INSERT INTO your_audit_table
   ( who, when, how, userid, NIN, ... etc )
  SELECT suser_name(), getdate(), 'INSERT', userid, NIN, ... etc
    FROM INSERTED 
go

Open in new window

0
AtourayAuthor Commented:
I did that but i am having the following error
Msg 311, Level 16, State 1, Procedure trg_audit_delete_biodata, Line 5
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Msg 207, Level 16, State 1, Procedure trg_audit_delete_biodata, Line 77
Invalid column name 'Data Verified'.
Msg 311, Level 16, State 1, Procedure trg_audit_update_biodata, Line 6
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Msg 207, Level 16, State 1, Procedure trg_audit_update_biodata, Line 74
Invalid column name 'Data Verified'.
Msg 311, Level 16, State 1, Procedure trg_audit_insert_biodata, Line 5
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Msg 207, Level 16, State 1, Procedure trg_audit_insert_biodata, Line 74
Invalid column name 'Data Verified'.
0
AtourayAuthor Commented:
this is my the trigger below
CREATE TRIGGER trg_audit_delete_biodata
 ON bio_Data
 FOR DELETE
AS
  INSERT INTO History
   ( username
      ,time_change
      ,how
      , userid
      , [NIN]
      ,[first_Name]
      ,[middle_Initial]
      ,[last_Name]
      ,[birthPlace]
      ,[birthdatetime]
      ,[occupation]
      ,[nationality]
      ,[permanent_address]
      ,[address]
      ,[employer_TIN]
      ,[next_of_kin]
      ,[datetime_created]
      ,[datetime_modified]
      ,[startdatetime]
      ,[end_datetime]
      
      ,[gender]
      ,[Permit_Type]
      ,[email]
      ,[next_of_kin_tel]
      ,[card_number]
      ,[sig_id_number]
      ,[entry_point]
      ,[datetime_of_entry]
      ,[addr_of_next_of_kin]
      ,[marital_status]
      ,[name_of_employer]
      ,[address_of_employer]
      ,[place_of_issue]
      ,[receiptNo]
      
      ,[Purpose of Entry]
      ,[NIN of Approving Officer]
      ,[NIN of Verifying Officer]
      ,[Date Approved]
      ,[Date Verified]
      ,[Father's Full Name]
      ,[Father's Place of Birth]
      ,[Mother's Full Name]
      ,[Mother's Place of Birth]
      ,[Authenticator's Full Name]
      ,[Authenticator's Profession]
      ,[Date Authenticated]
      ,[Document Produce]
      ,[duration]
      ,[details and address of properties in The Gambia]
      ,[Issued at]
      ,[Date Issued]
      ,[Passport No]
      ,[Dependant 1 FullName]
      ,[dependant 1 Relationship]
      ,[Dependant 2 FullName]
      ,[Dependant 2 Relationship]
      ,[Dependant 3 FullName]
      ,[Dependant 3 relationship]
      ,[Dependant 4 FullName]
      ,[Dependant 4 Relationship]
      ,[Dependant 1 age]
      ,[Dependant 2 age]
      ,[Dependant 3 age]
      ,[Dependant 4 age]
      ,[Document checked]
      ,[Approval Status]
      ,[Captured]
      ,[Data Entry Completed]
      ,[Handed to Cashier]
      ,[Data Verified] )
  SELECT 
      suser_name()
      , getdate()
      , 'DELETE'
	  ,userid
      , [NIN]
      ,[first_Name]
      ,[middle_Initial]
      ,[last_Name]
      ,[birthPlace]
      ,[birthdatetime]
      ,[occupation]
      ,[nationality]
      ,[permanent_address]
      ,[address]
      ,[employer_TIN]
      ,[next_of_kin]
      ,[datetime_created]
      ,[datetime_modified]
      ,[startdatetime]
      ,[end_datetime]
      
      ,[gender]
      ,[Permit_Type]
      ,[email]
      ,[next_of_kin_tel]
      ,[card_number]
      ,[sig_id_number]
      ,[entry_point]
      ,[datetime_of_entry]
      ,[addr_of_next_of_kin]
      ,[marital_status]
      ,[name_of_employer]
      ,[address_of_employer]
      ,[place_of_issue]
      ,[receiptNo]
      
      ,[Purpose of Entry]
      ,[NIN of Approving Officer]
      ,[NIN of Verifying Officer]
      ,[Date Approved]
      ,[Date Verified]
      ,[Father's Full Name]
      ,[Father's Place of Birth]
      ,[Mother's Full Name]
      ,[Mother's Place of Birth]
      ,[Authenticator's Full Name]
      ,[Authenticator's Profession]
      ,[Date Authenticated]
      ,[Document Produce]
      ,[duration]
      ,[details and address of properties in The Gambia]
      ,[Issued at]
      ,[Date Issued]
      ,[Passport No]
      ,[Dependant 1 FullName]
      ,[dependant 1 Relationship]
      ,[Dependant 2 FullName]
      ,[Dependant 2 Relationship]
      ,[Dependant 3 FullName]
      ,[Dependant 3 relationship]
      ,[Dependant 4 FullName]
      ,[Dependant 4 Relationship]
      ,[Dependant 1 age]
      ,[Dependant 2 age]
      ,[Dependant 3 age]
      ,[Dependant 4 age]
      ,[Document checked]
      ,[Approval Status]
      ,[Captured]
      ,[Data Entry Completed]
      ,[Handed to Cashier]
      ,[Data Verified]
    FROM DELETED 
go 
 
CREATE TRIGGER trg_audit_update_biodata
 ON bio_Data
 FOR UPDATE
AS
  INSERT INTO History
   ( username, time_change, how, [NIN]
      ,[first_Name]
      ,[middle_Initial]
      ,[last_Name]
      ,[birthPlace]
      ,[birthdatetime]
      ,[occupation]
      ,[nationality]
      ,[permanent_address]
      ,[address]
      ,[employer_TIN]
      ,[next_of_kin]
      ,[datetime_created]
      ,[datetime_modified]
      ,[startdatetime]
      ,[end_datetime]
      
      ,[gender]
      ,[Permit_Type]
      ,[email]
      ,[next_of_kin_tel]
      ,[card_number]
      ,[sig_id_number]
      ,[entry_point]
      ,[datetime_of_entry]
      ,[addr_of_next_of_kin]
      ,[marital_status]
      ,[name_of_employer]
      ,[address_of_employer]
      ,[place_of_issue]
      ,[receiptNo]
      
      ,[Purpose of Entry]
      ,[NIN of Approving Officer]
      ,[NIN of Verifying Officer]
      ,[Date Approved]
      ,[Date Verified]
      ,[Father's Full Name]
      ,[Father's Place of Birth]
      ,[Mother's Full Name]
      ,[Mother's Place of Birth]
      ,[Authenticator's Full Name]
      ,[Authenticator's Profession]
      ,[Date Authenticated]
      ,[Document Produce]
      ,[duration]
      ,[details and address of properties in The Gambia]
      ,[Issued at]
      ,[Date Issued]
      ,[Passport No]
      ,[Dependant 1 FullName]
      ,[dependant 1 Relationship]
      ,[Dependant 2 FullName]
      ,[Dependant 2 Relationship]
      ,[Dependant 3 FullName]
      ,[Dependant 3 relationship]
      ,[Dependant 4 FullName]
      ,[Dependant 4 Relationship]
      ,[Dependant 1 age]
      ,[Dependant 2 age]
      ,[Dependant 3 age]
      ,[Dependant 4 age]
      ,[Document checked]
      ,[Approval Status]
      ,[Captured]
      ,[Data Entry Completed]
      ,[Handed to Cashier]
      ,[Data Verified] )
  SELECT suser_name(), getdate(), 'UPDATE', [NIN]
      ,[first_Name]
      ,[middle_Initial]
      ,[last_Name]
      ,[birthPlace]
      ,[birthdatetime]
      ,[occupation]
      ,[nationality]
      ,[permanent_address]
      ,[address]
      ,[employer_TIN]
      ,[next_of_kin]
      ,[datetime_created]
      ,[datetime_modified]
      ,[startdatetime]
      ,[end_datetime]
      
      ,[gender]
      ,[Permit_Type]
      ,[email]
      ,[next_of_kin_tel]
      ,[card_number]
      ,[sig_id_number]
      ,[entry_point]
      ,[datetime_of_entry]
      ,[addr_of_next_of_kin]
      ,[marital_status]
      ,[name_of_employer]
      ,[address_of_employer]
      ,[place_of_issue]
      ,[receiptNo]
      
      ,[Purpose of Entry]
      ,[NIN of Approving Officer]
      ,[NIN of Verifying Officer]
      ,[Date Approved]
      ,[Date Verified]
      ,[Father's Full Name]
      ,[Father's Place of Birth]
      ,[Mother's Full Name]
      ,[Mother's Place of Birth]
      ,[Authenticator's Full Name]
      ,[Authenticator's Profession]
      ,[Date Authenticated]
      ,[Document Produce]
      ,[duration]
      ,[details and address of properties in The Gambia]
      ,[Issued at]
      ,[Date Issued]
      ,[Passport No]
      ,[Dependant 1 FullName]
      ,[dependant 1 Relationship]
      ,[Dependant 2 FullName]
      ,[Dependant 2 Relationship]
      ,[Dependant 3 FullName]
      ,[Dependant 3 relationship]
      ,[Dependant 4 FullName]
      ,[Dependant 4 Relationship]
      ,[Dependant 1 age]
      ,[Dependant 2 age]
      ,[Dependant 3 age]
      ,[Dependant 4 age]
      ,[Document checked]
      ,[Approval Status]
      ,[Captured]
      ,[Data Entry Completed]
      ,[Handed to Cashier]
      ,[Data Verified]
    FROM INSERTED 
go 
CREATE TRIGGER trg_audit_insert_biodata
 ON bio_Data
 FOR INSERT
AS
  INSERT INTO History
   ( username, time_change, how, [NIN]
      ,[first_Name]
      ,[middle_Initial]
      ,[last_Name]
      ,[birthPlace]
      ,[birthdatetime]
      ,[occupation]
      ,[nationality]
      ,[permanent_address]
      ,[address]
      ,[employer_TIN]
      ,[next_of_kin]
      ,[datetime_created]
      ,[datetime_modified]
      ,[startdatetime]
      ,[end_datetime]
      
      ,[gender]
      ,[Permit_Type]
      ,[email]
      ,[next_of_kin_tel]
      ,[card_number]
      ,[sig_id_number]
      ,[entry_point]
      ,[datetime_of_entry]
      ,[addr_of_next_of_kin]
      ,[marital_status]
      ,[name_of_employer]
      ,[address_of_employer]
      ,[place_of_issue]
      ,[receiptNo]
     
      
      ,[Purpose of Entry]
      ,[NIN of Approving Officer]
      ,[NIN of Verifying Officer]
      ,[Date Approved]
      ,[Date Verified]
      ,[Father's Full Name]
      ,[Father's Place of Birth]
      ,[Mother's Full Name]
      ,[Mother's Place of Birth]
      ,[Authenticator's Full Name]
      ,[Authenticator's Profession]
      ,[Date Authenticated]
      ,[Document Produce]
      ,[duration]
      ,[details and address of properties in The Gambia]
      ,[Issued at]
      ,[Date Issued]
      ,[Passport No]
      ,[Dependant 1 FullName]
      ,[dependant 1 Relationship]
      ,[Dependant 2 FullName]
      ,[Dependant 2 Relationship]
      ,[Dependant 3 FullName]
      ,[Dependant 3 relationship]
      ,[Dependant 4 FullName]
      ,[Dependant 4 Relationship]
      ,[Dependant 1 age]
      ,[Dependant 2 age]
      ,[Dependant 3 age]
      ,[Dependant 4 age]
      ,[Document checked]
      ,[Approval Status]
      ,[Captured]
      ,[Data Entry Completed]
      ,[Handed to Cashier]
      ,[Data Verified] )
  SELECT suser_name(), getdate(), 'INSERT', [NIN]
      ,[first_Name]
      ,[middle_Initial]
      ,[last_Name]
      ,[birthPlace]
      ,[birthdatetime]
      ,[occupation]
      ,[nationality]
      ,[permanent_address]
      ,[address]
      ,[employer_TIN]
      ,[next_of_kin]
      ,[datetime_created]
      ,[datetime_modified]
      ,[startdatetime]
      ,[end_datetime]
      
      ,[gender]
      ,[Permit_Type]
      ,[email]
      ,[next_of_kin_tel]
      ,[card_number]
      ,[sig_id_number]
      ,[entry_point]
      ,[datetime_of_entry]
      ,[addr_of_next_of_kin]
      ,[marital_status]
      ,[name_of_employer]
      ,[address_of_employer]
      ,[place_of_issue]
      ,[receiptNo]
     
      ,[Purpose of Entry]
      ,[NIN of Approving Officer]
      ,[NIN of Verifying Officer]
      ,[Date Approved]
      ,[Date Verified]
      ,[Father's Full Name]
      ,[Father's Place of Birth]
      ,[Mother's Full Name]
      ,[Mother's Place of Birth]
      ,[Authenticator's Full Name]
      ,[Authenticator's Profession]
      ,[Date Authenticated]
      ,[Document Produce]
      ,[duration]
      ,[details and address of properties in The Gambia]
      ,[Issued at]
      ,[Date Issued]
      ,[Passport No]
      ,[Dependant 1 FullName]
      ,[dependant 1 Relationship]
      ,[Dependant 2 FullName]
      ,[Dependant 2 Relationship]
      ,[Dependant 3 FullName]
      ,[Dependant 3 relationship]
      ,[Dependant 4 FullName]
      ,[Dependant 4 Relationship]
      ,[Dependant 1 age]
      ,[Dependant 2 age]
      ,[Dependant 3 age]
      ,[Dependant 4 age]
      ,[Document checked]
      ,[Approval Status]
      ,[Captured]
      ,[Data Entry Completed]
      ,[Handed to Cashier]
      ,[Data Verified]
    FROM INSERTED 
go

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

RiteshShahCommented:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AtourayAuthor Commented:
Thanks i have gone through your blog. but what do you think is causing the above errors mentioned?
0
RiteshShahCommented:
are you sure you have field name "Data Verified" in your table? I don't think so or may be spell mistake or may be not surrounded with square brackets.

for other error, I would like to know what SQL Server version are you using and what is the compatability level of your database?


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
this is sql 2005+ (aka sql 2008), right?
then, change your TEXT/NTEXT fields into VARCHAR(MAX) / NVARCHAR(MAX). same capacity. less problems.
0
RiteshShahCommented:
>>then, change your TEXT/NTEXT fields into VARCHAR(MAX) / NVARCHAR(MAX). same capacity. less problems.<<

100% agree with you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.