Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 The Yak Corral
 Trigger Madness

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

Posted - 2005-11-14 : 15:01:52
quote:
Originally posted by Michael Valentine Jones

I really wonder how much stuff like this is going on.



Do you really?

Isn't there enough pain in the world already?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 Optimizer
TG
Go to Top of Page

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
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-11-14 : 16:00:45
quote:
Originally posted by TG
Where 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".
Go to Top of Page

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.
Go to Top of Page

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!
Go to Top of Page

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 UPDATE
AS
DECLARE @MyPK int

SELECT @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.

Kristen
Kristen
Go to Top of Page

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_String
CHECK (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
Go to Top of Page

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
Go to Top of Page

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

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

Madhivanan

Failing to plan is Planning to fail




Hmmm, so the people interviewing them are idiots. That explains everything.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

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!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-15 : 09:17:38
I can think in sets of margaritas...though winter is fast approaching....

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2005-11-15 : 12:15:06
Winter is setting in?
Go to Top of Page

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!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-16 : 15:13:55
OK, I take it back, it hit 68F in NYC today

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

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
Go to Top of Page

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 ) + 32

quote:
Originally posted by Kristen

elwoos: what's the "F" thing that they put after their temperature figures?

Kristen



CODO ERGO SUM
Go to Top of Page
    Next Page

- Advertisement -