Results 1 to 10 of 10

Thread: Using Triggers SQL DataBase

  1. #1

    Thread Starter
    Hyperactive Member yousufkhan's Avatar
    Join Date
    Jan 2002
    Location
    India
    Posts
    492

    Lightbulb Using Triggers SQL DataBase

    I need some advise on using triggers

    I have a table called receipts and their are 2-3 more tables
    when their will be insert insert or update in the receipts table
    some information which is getting inserted into receipt table
    will also be inserted or updated in other talbes
    for this purpose shal i use triggers to insert or update in others table
    will it be good reliable and acurate to use triggers

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Using Triggers SQL DataBase

    You could go either way. DBAs will often tell you to do as much as possible in the database and application developers will tell you the opposite. Sometimes the nature of the data makes one option more appropriate than the other. As long as the data required is available to the trigger though, using triggers will work.

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Triggers SQL DataBase

    jmcilhinney is correct. From a programmers point of view why break up the logic versus doing it in one place whether it is a stored procedure or in an application? It is more maintainable to me to be in one spot. I don't know how many times I have debugged code I didn't write and while debugging what was going wrong wasn't in the stored procedure or the code. While I'm scratching my head I think "maybe they put it in a trigger". Opps...there it is, Grrrrrr

    The only place I realy use triggers is to write to archive files or if the same logic needs to execute everytime a row is saved and it would be clunky to repeat the code all over the place.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Using Triggers SQL DataBase

    Personally I dislike triggers. Unless you can write them perfectly clean the first time, they can be a pain to debug. They also have a tendency to hide logic since there is not obvious notation that says "hey, there's a trigger on this table" ... When it comes to data, I'm something of a control freak... so I tend to want to put all my data logic in the SProc. The only time I "use" triggers is for auditing purposes. And that's mostly because sometimes the client will get into the database and manipulate things directly (which is fine... and why we have the auditing).

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: Using Triggers SQL DataBase

    Quote Originally Posted by techgnome View Post
    The only time I "use" triggers is for auditing purposes. And that's mostly because sometimes the client will get into the database and manipulate things directly (which is fine... and why we have the auditing).
    Same here.

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Triggers SQL DataBase

    Quote Originally Posted by techgnome View Post
    Personally I dislike triggers. Unless you can write them perfectly clean the first time, they can be a pain to debug. They also have a tendency to hide logic since there is not obvious notation that says "hey, there's a trigger on this table" ... When it comes to data, I'm something of a control freak... so I tend to want to put all my data logic in the SProc. The only time I "use" triggers is for auditing purposes. And that's mostly because sometimes the client will get into the database and manipulate things directly (which is fine... and why we have the auditing).

    -tg
    One nicw thing about the SQL step through debugging, once you get it working, is when you step through the stored procedures if you hit a trigger that code pops up and you can step through it. I found a couple of triggers I didn't know were in place that way.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Using Triggers SQL DataBase

    yeaaaah.... IF you have access to the db like that... sadly I don't... some of our clients databases are hosted elsewhere, and we can't even run SQL Profiler (facepalm) ... or the database could be hosted by the client themselves, in which case it can be a major pain to get that opened up... assuming it can be opened up. Besides, I wasn't aware that the SQL step through also stepped through triggers... plus also, wouldn't you also need the trigger script to "step through" it? I don't have a lot of experience with it because it's not something normally available to me.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Triggers SQL DataBase

    Quote Originally Posted by techgnome View Post
    yeaaaah.... IF you have access to the db like that... sadly I don't... some of our clients databases are hosted elsewhere, and we can't even run SQL Profiler (facepalm) ... or the database could be hosted by the client themselves, in which case it can be a major pain to get that opened up... assuming it can be opened up. Besides, I wasn't aware that the SQL step through also stepped through triggers... plus also, wouldn't you also need the trigger script to "step through" it? I don't have a lot of experience with it because it's not something normally available to me.
    I'm not sure what you mean by "trigger script". In the SQL debugger that comes with MS SQL (2000 had it, 2005 got rid of it, and now it is back) you step through the code just like you would in IDE. There are some differences and limitations than what is available in Visual studio. In Visual studio 2010 and 2012 you connect to the database, feed in the parameters, and step through the code. If there is a stored procedure that modifies tables that have a trigger that code is also stepped through automatically.

    It beats the hell out of print statements and the like. We only turn it on from MS SQL management studio in the developement and test regions here. From Visual Studio you need SA rights so we don't have that set up in test.

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Using Triggers SQL DataBase

    Yeah, I can see how great that is... if you have that level of access to the database... which we often don't... so as a matter of policy (and to some extent personal disdain) we avoid triggers when we can. One of the biggest problems with triggers is that they cannot be run isolated... like a section of a sproc can be... I can take any sproc, rip a part of it out, declare my variables, set them and run/test it... triggers sadly... well... usually cannot.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: Using Triggers SQL DataBase

    Quote Originally Posted by techgnome View Post
    Yeah, I can see how great that is... if you have that level of access to the database... which we often don't... so as a matter of policy (and to some extent personal disdain) we avoid triggers when we can. One of the biggest problems with triggers is that they cannot be run isolated... like a section of a sproc can be... I can take any sproc, rip a part of it out, declare my variables, set them and run/test it... triggers sadly... well... usually cannot.

    -tg
    Yeah SPROCs are easier to test. Sometimes in applications I'll create a stored procedure as a global temporary stored procedure (prefix it with ##) that is the exposed to Visual Basic. Play with it against production data\code until I get it right and then ask the DBAs to move it (not doing updates).

    That's one of the things DBAs don't like to know programmers do

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width