Generating history trigger with EF , edmx and TT files
I have wrote in an older post ( http://msprogrammer.serviciipeweb.ro/2010/06/28/ef-automatic-history-of-table-and-t4-files-tt-files/ ) how to generate history code for tables . The easy solution was to create a tt file that track for the ObjectContext the SaveChanges for each table that has a “history” in name. the limitation is that , when you raise an sql command such as “update table ” you must load from database a lot of rows for this….
Now I want to show the same thing, but generating triggers in database for that ! I start also from edmx file and with a template stealed from http://forums.asp.net/p/1599616/4083198.aspx ( to have type of fields in the database ) and another stealed and modified from StackOverflow(to generate trigger for after insert , update, delete) I manage to have a solution to generate sql tables and trigger code.
When is that good ?At the beginning stages of a project when the table structure changes by adding a new parameter.
Sample code generated for table Notes(ID, TextNote, PersonID)
print 'Create table Notes_History '; Create Table Notes_History( ID_Notes_History BIGINT IDENTITY NOT NULL ,History_Action varchar(50) ,History_From varchar(100) default HOST_NAME() ,History_User varchar(50) default SYSTEM_USER ,History_Date varchar(50) default getdate() ,Id int NOT NULL ,Textnote nvarchar (255) NULL ,PersonId int NULL ) print 'end Create table Notes_History '; GO print 'create trigger for Notes' GO CREATE TRIGGER dbo.TR_IUP_Notes ON Notes AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; DECLARE @Ins int DECLARE @Del int SELECT @Ins = Count(*) FROM inserted SELECT @Del = Count(*) FROM deleted if(@Ins + @Del = 0) return; declare @operation varchar(50) set @operation ='update'; if(@ins < @del) set @operation ='delete'; if(@ins > @del) set @operation ='insert'; if(@ins <= @del) begin INSERT INTO Notes_History(History_Action ,Id,Textnote,PersonId) select @operation ,Id,Textnote,PersonId from deleted end else begin INSERT INTO Notes_History(History_Action ,Id,Textnote,PersonId) select @operation ,Id,Textnote,PersonId from inserted end END
The drawback of the code : the user that executes is not always the logged sql server user…
Anyway, what you have to do to use this automatically generated history/audit for tables ?
download historysql and modify
string inputFile = @”Model1.edmx”;
from historysql.tt to your edmx name. Then take the generated code and execute in sql server.