17

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?

Dmitri
  • 171
  • 1
  • 1
  • 3

2 Answers2

22

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.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • in my current scenario I need to update a "DateModified" column in one of the tables. the value of "DateModified" will not be used by the current context. – Dmitri May 06 '11 at 15:40
  • actually thinking about it, since the POCO objects are tracked by the context, can I leverage that to update my timestamp? – Dmitri May 06 '11 at 15:41
  • *"DateModified" will not be used by the context* doesn't correspond with code-first approach where you define only columns which are used by the context. – Ladislav Mrnka May 06 '11 at 15:49
  • You can of course change `DateModified` in context before you save changes by overriding `SaveChanges` iterate all modified entities and set the field. – Ladislav Mrnka May 06 '11 at 15:50
  • "DateModified" would be used but not in the _current_ context. thanks for all the info! – Dmitri May 06 '11 at 16:07
1

I'm not entirely sure what you mean. If you want an actual SQL trigger on the table, then I'd create the trigger, as normal, in SQL. If you mean you want some kind of processing/updating to occur in your code whenever one of you entity sets is modified, then I think you'd want to do a custom method that would be called whenever that entity set is updated.

AllenG
  • 8,112
  • 29
  • 40
  • 2
    I do need an actual trigger on a table. If I create it directly in SQL it will be lost because of this "DropCreateDatabaseIfModelChanges". – Dmitri May 06 '11 at 15:22
  • If it's a requirement to have a SQL Trigger, you could always create a script and just run it when you re-create your tables. That's kind of yucky, though. Ladislav makes a good point- any time your trigger runs, you'll need to refresh your context. If it can be done in code instead, I think you'll save yourself some headaches. – AllenG May 06 '11 at 15:32
  • 2
    That makes no sense at all... Why not leverage SQL vs making a secondary call out to the DB from code? It's less code to write and to execute, as well, it makes SQL do what it's supposed to do, aka, triggers. Not to mention, it's one less call out to the DB. – IyaTaisho Feb 27 '13 at 16:14