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?
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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
 
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
All Courses

From novice to tech pro — start learning today.