Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 8 down vote accepted

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 )
share|improve this answer
    
My problem doesn't refer to business rules, web services, access control nor is it non-transactional. So my interpertation of your answer is that it's OK. I'll also make sure to document the triggers' functionality extensively. Thanx for the crystal clear answer! –  Benny Skogberg Aug 1 '11 at 16:36
1  
@Benny - NO NO NO - What you are describing looks like business logic. Unless that is really just some sort of change/audit log that will never be used by any other business logic then he is not saying that it is OK. –  Chad Aug 2 '11 at 13:54
add comment

Triggers are a powerful tool, and like any other tool you need to be careful when using them.

  1. When you make a mistake in a trigger things can go DRASTICALLY wrong and unless you are running traces you won't realise...

  2. They do 'magically' change/insert/delete data that the user of the DB (the current application/any future application/a developer doing a one off update) did not realise/intend.

The big problem is that after you've created the trigger, it is not obvious to other developers/users that a trigger is even there and what it does.

That said, they are a great tool for maintaining integrity of your data and for true Auditing of changes.

You need to ask yourself if the logic you want to put in the trigger sits best with the application(s) or within the DB, weighing up the risks on both sides (what happens if a new application comes along and doesn't enforce this rule?)

share|improve this answer
    
I appriciate your wider view of thinking in this matter. I'll adress this issue to my fellow colleagues when I'm back at work on monday. Thanx! –  Benny Skogberg Aug 3 '11 at 9:10
    
@Benny Skogberg You're welcome. Thanks for the comment :-) –  Andrew Bickerton Aug 3 '11 at 12:26
add comment

Yes, it is wise. That's the purpose of a trigger actually, to perform needed actions after an insert/update/delete operation upon a table.

You need to take into account the fact that a trigger in MS SQL will not handle each row separately, but will handle all rows of the current transaction at once. So if an operation will insert 10 rows at once, you'll need to think your trigger's code to treat all rows at once.

share|improve this answer
    
Thanx Marian! I'll keep your advice in mind when I implement the trigger. –  Benny Skogberg Aug 1 '11 at 11:24
1  
It is fine for Benny but what about the guy who will come in behind benny and clean up his mess. Your post is a wash because +1 for nothing the multi row affect but -1 for saying it is wise. –  Chad Aug 2 '11 at 13:57
1  
As long as the service that's populating the database is not doing all that the man needs and as long as he can't change the service (that's my feeling after the description), and the question is here on DBA.SE, not on programmers, I'll say that a trigger that's well documented shouldn't be any problem. He said he's doing some things manually, so code change couldn't be involved from what I understand.. –  Marian Aug 2 '11 at 19:13
1  
In this case it may be the only viable option, that doesn't make it wise. The wise thing to do would be to push back on the application developer and get them to correctly insert into both tables. –  Matthew Watson Aug 3 '11 at 2:51
    
cough. I see where this is heading. Sorry for beeing unclear in my question above. I do have the possibility to change the integration service, even if it's not "owned" by me. From what I can read it's better to change the integration service and only use triggers for auditing/history record services. –  Benny Skogberg Aug 3 '11 at 6:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.