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)

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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.