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
)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]
ALTER TABLE [dbo].[tblChild] ADD CONSTRAINT [DF_tblChild_DateAdded] DEFAULT (getdate()) FOR [DateAdded]
The log should be written to my audit table. You can come up with the structure of the audit table.
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(
OBJECT_ID(TABLE_SCHEMA + '.' + @TableName),
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.
8 freelancers are bidding on average $138 for this job
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