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.


by

Tags:

Comments

5 responses to “Generating history trigger with EF,edmx and TT files”

  1. fenderbirds Avatar
    fenderbirds

    nice article, keep the posts coming

  2. rugslinger Avatar

    I’ll have to go back and read all your previous posts now.

  3. badmash Avatar
    badmash

    I just signed up to your blogs rss feed. Will you post more on this subject?

    1. Andrei Ignat Avatar
      Andrei Ignat

      I am planning to have more – because the solution now is only partial.

  4. Norman Bailer Avatar

    I wanted to construct a quick message to say thanks to you for the splendid tricks you are giving at this website. My extensive internet look up has at the end of the day been compensated with extremely good information to go over with my visitors. I would suppose that most of us website visitors are really lucky to live in a decent community with so many special professionals with helpful tips. I feel extremely happy to have encountered your web page and look forward to plenty of more thrilling times reading here. Thank you once more for all the details.

Leave a Reply

Your email address will not be published. Required fields are marked *