Completed

create triggers for audit trail in sql server

I have a few tables in my sql server 14.0 database that I want to monitor. I want to add triggers after insert, update and delete to these few tables. I want a log of every record inserted, updated, or deleted. One of the tables I would like to monitor is tblChild. Its definition is below.

CREATE TABLE [dbo].[tblChild](

[ChildId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,

[User] [numeric](18, 0) NULL,

[FirstName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

[GradeLevel] [nvarchar](50) NULL,

[DOB] [date] NULL,

[PreviousExperiance] [nvarchar](max) NULL,

[DateAdded] [datetime] NULL,

[AutoReenroll] [bit] NULL,

[Needs] [nvarchar](50) NULL,

[NotesForRoster] [nvarchar](max) NULL,

CONSTRAINT [PK_tblChild] PRIMARY KEY CLUSTERED

(

[ChildId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblChild] ADD CONSTRAINT [DF_tblChild_DateAdded] DEFAULT (getdate()) FOR [DateAdded]

GO

The log should be written to my audit table. You can come up with the structure of the audit table.

Specifically:

If a record is inserted into tblChild, all of the fields’ values should be recorded in the log (audit table).

If a record is deleted from tblChild, all of the fields’ values should be recorded in the log (audit table).

If a record is updated in tblChild, all of the changed values should be recorded in the log (audit table), both old values and new values.

The trigger should be smart, and loop through the fields using an approach similar to:

SELECT @field = MIN(

COLUMNPROPERTY(

OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),

COLUMN_NAME,

'ColumnID'

)

)

FROM [login to view URL]

So I don’t have to change the trigger code if I add or rename a column.

Also, the trigger should be smart and not allow an sql injection problems.

Skills: Microsoft SQL Server

See more: sql server audit trigger xml, sql server audit select statements, sql audit trigger generator, sql server dml triggers to track all database changes, sql server audit trigger for insert, update, delete, sql server audit user activity, sql server auditing best practices, sql server trigger audit columns_updated, create sql server table csv file, create csv file dts sql server 2005, sql server create tabel csv file, type exchange mailbox create sql server, sql server create table graphically cnet dbo, create xml type table sql server using vbnet, asp audit con sql server, create form submit aspnet sql server, create admin login aspnet sql server, sql server null default, create script backup database sql server, create attendance taking system sql server

About the Employer:
( 7 reviews ) gaithersburg, United States

Project ID: #25594001

Awarded to:

mostafaemad75

Dear Hiring Manager, I have +8 years experience in .NET, .NET CORE and SQL SERVER throw this period I have submitted thousands hours in coding. My main experience in web development field. I have +2 years experience More

$50 USD in 1 day
(1 Review)
1.4

8 freelancers are bidding on average $138 for this job

sumon355

Hello, As an expert in MSSQL, i am very much interested to create the required triggers in your tables. Looking forward to have a positive response from you. Thanks

$240 USD in 3 days
(135 Reviews)
7.4
schoudhary1553

Hello, I can help you with create triggers for audit trail in sql server I have gone through your job posting and become very much interested to work with you. I am an expert in this field. I have already completed s More

$180 USD in 2 days
(3 Reviews)
5.3
SergioAlfaroV

Hello! I am a Data Engineer, MS SQL certified MCSA / MCSE, with 14 years of experience writing SQL queries, reports/dashboards design and construction, database administration, data warehousing, and database related pr More

$150 USD in 1 day
(18 Reviews)
4.8
ahaseeb7838

Hi, hope you are fine. I have gone through your job description, You want to create an audit table where you want to store all the changes made in "tblchild". In short you want to capture all the changes. I'm a Data En More

$100 USD in 1 day
(1 Review)
3.3
mani7709

Hi! I have got plenty of experience in create triggers for audit trail in sql server and i have checked the details thoroughly i.e I have a few tables in my sql server 14 I am an expert Microsoft SQL Server coder. Ple More

$185 USD in 9 days
(0 Reviews)
0.0
oscartovaco0725

Hello! I am very interested in your project because I have an experience similar to your project. One example of similar projects that I’ve handled in the past includes [login to view URL], where I helped my client successfully More

$50 USD in 1 day
(0 Reviews)
0.0
DSam91

Hello, I have 4 years of experience working as a Software Engineer with Microsoft Technologies including .NET Framework and MSSQL 2008-2019. I also have an MSc in Data Science. Thus, I am confident I can complete your More

$150 USD in 7 days
(0 Reviews)
0.0