SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Trigger Madness
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 5

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/14/2005 :  14:52:00  Show Profile  Reply with 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”?

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 - 11/14/2005 :  15:01:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/14/2005 :  15:04:41  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 11/14/2005 :  15:23:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/14/2005 :  15:37:19  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 11/14/2005 :  15:53:07  Show Profile  Reply with Quote
>>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 - 11/14/2005 :  16:00:45  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 11/14/2005 :  16:40:16  Show Profile  Visit nr's Homepage  Reply with 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. 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
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 11/14/2005 :  17:16:15  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 11/14/2005 :  21:25:37  Show Profile  Reply with Quote
"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)

USA
7020 Posts

Posted - 11/14/2005 :  22:05:38  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 11/14/2005 :  23:17:30  Show Profile  Reply with Quote
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

India
22761 Posts

Posted - 11/15/2005 :  02:17:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 11/15/2005 02:18:44
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 11/15/2005 :  02:23:20  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 11/15/2005 :  03:09:57  Show Profile  Reply with Quote
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 - 11/15/2005 :  09:17:38  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 11/15/2005 :  12:15:06  Show Profile  Reply with Quote
Winter is setting in?
Go to Top of Page

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 11/16/2005 :  03:50:40  Show Profile  Reply with Quote
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 - 11/16/2005 :  15:13:55  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 11/16/2005 :  15:59:21  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 11/16/2005 :  16:09:06  Show Profile  Reply with Quote
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
Page: of 5 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000