| Author |
Topic |
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
 Posted - 2005-11-14 : 14:52:00
|
| Is there something about triggers that attracts people with very little experience with SQL? I see a lot of questions posted here from people that obviously have little experience even with stored procedures who are trying to use triggers for things that make me cringe. Is it just a matter of “fools rush in where wise men fear to tread”?I only use triggers when needed for very specific things, like audit tables. When I see people wanting to do things in triggers like launch applications, send email, or create stored procedures, I really wonder how much stuff like this is going on.CODO ERGO SUM |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-14 : 15:04:41
|
| A rookie knows what CAN be done.An expert knows what SHOULD be done.Knowledge of database application best-practices is only acquired over time, and not usually from perusing Books Online. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-14 : 15:23:55
|
True enough, but it doesn't explain why triggers are a bad practice of first resort. What is it that rookies find so attractive about triggers?quote:
Originally posted by blindman
A rookie knows what CAN be done.An expert knows what SHOULD be done.Knowledge of database application best-practices is only acquired over time, and not usually from perusing Books Online.
CODO ERGO SUM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-11-14 : 15:37:19
|
| I don't know, maybe because "trigger" just sounds so cool. You know, like guns and Roy Rogers' horse and stuff. Much more exiting than "foreign key constraint"My problem is I never know how to respond to questions like:-------------------topic: help with syntax error question: i'm trying to exec some dynsql inside a few nested cursors for a new trigger on one of the tables our accounting software uses and I'm getting a syntax error on this line:exec ('truncate table @SQL')-------------------Where do you begin?Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-14 : 15:53:07
|
| >>maybe because "trigger" just sounds so cool.Maybe they should should remame them to "landmines", so it sounds like something you'll blow your foot off with.CODO ERGO SUM |
|
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2005-11-14 : 16:00:45
|
quote:
Originally posted by TGWhere do you begin?
I usually begin with a dope slap. Alternatively, you can hold up your hand, like you are stopping someone, and tell them "Run into this". |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-14 : 16:40:16
|
| It's because they can add/ update a table and use that to cause lots of updates in the database and it seems like the sort of sequential processing you do in an application. Of course it's not which causes the problems.I worked on one system (which was failing) which worked by an insert into a table. This table had a trigger on it which did all the processing depending on fields in the inserted row - with other triggers cascading processing from other tables. Biggest problem of course was that it was all one transaction and no way out of it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-11-14 : 17:16:15
|
quote:
Maybe they should should remame them to "landmines", so it sounds like something you'll blow your foot off with.
Hillarious. I love it! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-14 : 21:25:37
|
"bad practice of first resort"I gotta remember that one ...CREATE TRIGGER MyTrigger ON dbo.MyTable FOR UPDATEASDECLARE @MyPK intSELECT @MyPK FROM inserted... <fx:knocks head against wall>We've got triggers on all our tables to:a) Make sure that the Create Date and Update Date columns are set (but NOT to physically set the Update Date - that's done in the SProcs these days, used to be in Trigger though ...)b) To make sure that varchar columns which are empty strings get coerced to NULL.Which is costing us LOTS of CPU cycles. So I'm gonna replace them with RAISERROR triggers in DEV, and nothing in Production.Bad design mistake I should'a known better about.KristenKristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-14 : 22:05:38
|
Instead of RAISERROR triggers, how about a check constraint?ADD CONSTRAINT CHK_MyCol_Not_Empty_StringCHECK (MyCol is NULL or MyCol <> '')quote:
Originally posted by Kristen..To make sure that varchar columns which are empty strings get coerced to NULL.Which is costing us LOTS of CPU cycles. So I'm gonna replace them with RAISERROR triggers in DEV, and nothing in Production...
CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-14 : 23:17:30
|
Hmmm ... well I'm happy to allow them in Production if they occur, but I want to stamp out the ones we find in DEV. Is that a really REALLY crappy answer?  Kristen |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-11-15 : 02:17:36
|
quote:
Is there something about triggers that attracts people with very little experience with SQL? I see a lot of questions posted here from people that obviously have little experience even with stored procedures who are trying to use triggers for things that make me cringe. Is it just a matter of “fools rush in where wise men fear to tread”?
It is because newbies are interested to learn Triggers as most of the interview questions are on Triggers  MadhivananFailing to plan is Planning to fail |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-15 : 02:23:20
|
quote:
Originally posted by madhivanan
quote:
Is there something about triggers that attracts people with very little experience with SQL? I see a lot of questions posted here from people that obviously have little experience even with stored procedures who are trying to use triggers for things that make me cringe. Is it just a matter of “fools rush in where wise men fear to tread”?
It is because newbies are interested to learn Triggers as most of the interview questions are on Triggers MadhivananFailing to plan is Planning to fail
Hmmm, so the people interviewing them are idiots. That explains everything.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-11-15 : 03:09:57
|
quote:
It's because they can add/ update a table and use that to cause lots of updates in the database and it seems like the sort of sequential processing you do in an application.
I think Nigel has hit the nail on the head here. People (at least initially) find sets hard to think about, maybe it reminds them too much of maths (strange that huh!) but they think with a trigger they have more control over what's really happening or that they can understand it, isn't that why cursors are so often used?Does it take a special type of mindset (sic.) to think in sets? Maybe an answer is to make all programming languages set based VB.SET anyone  steve-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-15 : 12:15:06
|
| Winter is setting in? |
|
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-11-16 : 03:50:40
|
| You mean New York will be wintery in December! Right that's it - my holiday is off-----------Facts are meaningless. You could use facts to prove anything that's even remotely true! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-16 : 15:59:21
|
| elwoos: what's the "F" thing that they put after their temperature figures? Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-16 : 16:09:06
|
It's a convenient shorthand for ( 1.8 * C ) + 32quote:
Originally posted by Kristen
elwoos: what's the "F" thing that they put after their temperature figures? Kristen
CODO ERGO SUM |
|
|
|
Next Page
|
Trigger Madness