3

We have created a table with a trigger that updates a ModifiedDate field in it each time there is an UPDATE/INSERT. For a particular transaction in our app, though, we would like to temporally disable this trigger.

Would it be possible to do this directly from LINQ? (Directly, not calling a stored procedure)

salgiza
  • 5,832
  • 2
  • 26
  • 32
  • 2
    warning, warning! disabling a trigger WILL affect any other UPDATEs that run at the same time! this (disable trigger) sounds like a hack to a bad design, so I'll offer up a crappy hack to solve this. update the date in the application to something like 1/1/1900, then in your trigger realize this bogus date, ignore the standard date update logic and set it back to the DELETED.yourDateColumn, thus restoring your date. – KM. Feb 23 '10 at 13:14
  • Arg. You are right. I've never had a similar problem before and I hadn't noticed that the solutions using SQL disabled the triggers for the table and not for the scope of the transaction. It indeed intended to be a fast hack to solve a change in requirements at the end of a project (we now need to perform a maintenance task in the records of the DB, but being an internal process the customer doesn't want the "ModifiedDate" field to change). I'm not a big fan of hacks, though, and you have convinced me. We'll remove the triggers altogether and update the field only when needed. Thanks! – salgiza Feb 23 '10 at 15:38

1 Answers1

1

Not using the generated code. You could hook into a stored procedure, but that may involve extra work.

For "last updated" data, it is possible to hook into the DataContext's change-set that is obtained during the SubmitChanges method - so it is possible set properties (perhaps based on an interface) for the rows that are being submitted for UPDATE.

I have a similar issue, where my audit triggers behave differently based on which columns are part of the UPDATE statement (via UPDATE(ColumnName) in the trigger), so in a similar question I needed to hack the data-context to always consider a specific column dirty. That may have some cross-over.

But no: I don't think you can disable the triggers completely.

Community
  • 1
  • 1
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900