SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


More Triggers


More Triggers

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: Administrators
Points: 220708 Visits: 19607
Comments posted to this topic are about the item More Triggers

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Nakul Vachhrajani
Nakul Vachhrajani
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4228 Visits: 2151
Hello!

Happy Friday :-)!

I am of the opinion to have 1 trigger for business logic (per operation - Insert/Update/Delete) and 1 trigger for auditing. The prime reason being - it is easier to manage that way. Also, in the systems that I work with the amount of business logic happening in triggers is minimal, which makes this approach feasible to use.

Have a good week-end, everyone!

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
a.yoganand
a.yoganand
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 Visits: 60
Hi
I agree with Nakul. Also we can use triggers for validating complex data constraints (based on buisness logic) which cannot be done using normal constraints.

Regards
Yoganand
:-)
Malcolm Currey
Malcolm Currey
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 110
I also belive in multiple triggers. I have one trigger for auditing, plus additional triggers (often more than 1) for other purposes. I find it is easier to manage the triggers this way.

Mal
Carlo Romagnano
Carlo Romagnano
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9268 Visits: 3439
I prefer multiple triggers. But, it's easier to fall in recursive triggers.
jfmccabe
jfmccabe
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1037 Visits: 440
I've always preferred to use views rather than tables for most of the grunt work so I end up with business logic in the view triggers and auditing in the table triggers.

We don't audit every table so that keeps it simple and we only have a few updatable views. Nobody has permissions on tables apart from developers and all the permissions are on the views.



paul s-306273
paul s-306273
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4310 Visits: 1190
I think a separate trigger for auditing is definitely preferable.
TravisDBA
TravisDBA
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4474 Visits: 3069
paul s-306273 (11/12/2010)
I think a separate trigger for auditing is definiteley preferable.


Me too Paul. I also like to know who (or prevent) might be modifying tables, so I like DDL triggers as well. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Steve-3_5_7_9
Steve-3_5_7_9
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4472 Visits: 1611
As with most of the replies, I use one trigger for Auditing and one or more triggers for "other". I prefer to limit my trigger use, so I have minimal "other" triggers, but always have an "audit" trigger on DML tables.

It's easier to manage when triggers are functionally separate.



schleep
schleep
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4606 Visits: 1452
If possible, I prefer to avoid triggers altogether.
Having said that, on tables where business rules can be enforced using DRI and constraints, I use them for auditing.

However, my developers have been slow to move some of our apps to 100% stored procs, so some triggers are still in use for business rules in the meantime. As the apps move to stored procs, the triggers logic are phased out.

1 trigger / action.

Happy Friday.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search