EF 4.1 code-first adding a trigger to a table

Question

What is the best way to add a trigger to a table created by code-first approach with EF 4.1?

A way I'm cosidering is to execute a custom SQL query either in OnModelCreating or when Db context is being initialized.

Is there a better idea?

Answer 1

Using custom initializer which will execute CREATE TRIGGER SQL command is the only option if you want EF to create trigger for you (similar code like here). Also don't forget to include SET NOCOUNT ON at the beginning of the trigger code.

But there is more complex problem related to trigger's logic. If you want trigger which will modify data passed to the database you must understand that changes done by trigger will not be reflected in your current context. Context will still only know entity with data you passed to the database. This is normally solved by setting properties modified by trigger as DatabaseGeneratedOption.Computed for update or DatabaseGeneratedOption.Identity for insert. In such case you cannot modify properties in your application and they must be modified in the database. EF will ensure that these properties are selected after modification and passed to the entity. The problem is that this doesn't work with code-first.