CODEWITHSUNDEEP
c#asp.net-mvcentity-framework
user7592671
user7592671

Reputation: 310

Track Changes of an entity / object

I would like to know what the options are to track changes in an entity or object within an MVC application using C# and entity framework?

I have read that a common recommendation is to use property change events to alert when a change happens and then I could potentially do what I need to do on that event.

However I sort of need it to automatically as well newly created items as well, and not just existing properties.

Basically I want an audit trail of what happened and who initiated it to happen. only track items that are written to the database and basic auditing, like if a company name changed, what was it changed from, what was it changed to, by whom was it changed and when....

what approach is available other than property change, or is there a tool that I can use?

Upvotes: 1

Views: 1633

Answers (1)

Ben
Ben

Reputation: 35613

You want an audit trail, so this is something which needs to be done at the database level not at the MVC level.

Every table should have an "updated" column containing the exact UTC date and time of the update, and an "updatedBy" column containing the identity of the user who last updated.

Every time a table changes, you should have a trigger copy the row version to an archive (audit) table. Then you can consult that audit trail at any time to find out who made the most recent change, and also to see any previous version of the row, and even any point-in-time view of the table.

The audit table can (should) be maintained automatically by a trigger.

E.g.:

create table Animal (
    id integer primary key,
    name nvarchar(max),
    description nvarchar(max),
    updated datetime,
    updatedBy nvarchar(max)
)

create table AnimalAudit (
    id integer,
    name nvarchar(max),
    description nvarchar(max),
    updated datetime,
    updatedBy nvarchar(max),
    updateAction char(1),
    primary key (id, updated)
)


create trigger TRG_Animal_Audit 
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT AnimalAudit
    select id, name, description, updated, updatedBy, 'C' from INSERTED where not exists (select 1 from DELETED where INSERTED.id = DELETED.id);
    INSERT AnimalAudit
    select id, name, description, updated, updatedBy, 'U' from INSERTED where exists (select 1 from DELETED where INSERTED.id = DELETED.id);
    INSERT AnimalAudit
    select id, name, description, updated, updatedBy, 'D' from DELETED where NOT exists (select 1 from INSERTED where INSERTED.id = DELETED.id);

END

Upvotes: 1

Related Questions