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 (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: Administrators
Points: 63035 Visits: 19112
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
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2266 Visits: 2145
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 (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 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 (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 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
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4974 Visits: 3326
I prefer multiple triggers. But, it's easier to fall in recursive triggers.

I run on tuttopodismo
jfmccabe
jfmccabe
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2426 Visits: 1115
I think a separate trigger for auditing is definitely preferable.
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1998 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
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 1599
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
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: 1020 Visits: 1327
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