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.
nice article, keep the posts coming
I’ll have to go back and read all your previous posts now.
I just signed up to your blogs rss feed. Will you post more on this subject?