Using Triggers SQL DataBase

[yousufkhan]

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

[jmcilhinney]

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.

[TysonLPrice]

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.

[techgnome]

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

[jmcilhinney]

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.

[TysonLPrice]

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.

[techgnome]

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

[TysonLPrice]

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.

[techgnome]

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

[TysonLPrice]

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