Sql-server – Wise to use trigger to update another table

sql servertrigger

I have an Object table which is populated from an Integrated service (which I can change if needed) from another database. At certain points we need to manually add posts in another table ObjectObjectGroup (ObjectId, ObjectGroupId) which is needed if Object.ObjectType have a certain integer value. Since the integration service doesn't handle that kind of update, I'm thinking of adding a trigger to the Object table which in pseudo-code would be the following:

if Object.ObjectType = 10
    begin
        if Object.ObjectNumber like '<string pattern>'
        begin
            insert into ObjectObjectGroup values...
        end
    end

Is this setup wise, or is there a better way in terms of performance?

Best Answer

Mostly Copy/Pasting my response from this question on stackoverflow

Triggers can be very alluring, when you first start using them they seem like a magic bullet to all kinds of problems. But, they make "magic" stuff happen, if you don't know the database inside out, it can seem like really strange things happen (such as inserts into other tables, input data changing, etc). Before implementing things as a trigger I'd seriously consider instead enforcing the use of an API around the schema (preferably in the database, but outside if you can't).

Some things I'd still use triggers for

  • Keeping track of "date_created" and "date_last_edited" fields
  • Inserting "ID"'s (in oracle, where there is no auto id field)
  • Keeping change history

Things you wouldn't want to use triggers for

  • business rules/logic
  • anything which connects outside of the database (eg a webservice call)
  • Access control
  • Anything which isn't transactional ( anything you do in the trigger MUST be able to rollback with the transaction )
Related Question