Triggers 2

Question

Post reply

Triggers 2

Hugo Kornelis

SSC Guru

Points: 64790
More actions
April 24, 2013 at 8:39 pm

#394667

Comments posted to this topic are about the item Triggers 2
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/
April 25, 2013 at 2:05 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

kapil_kk SSC-Insane Points: 21316 More actions · Answer 1

Good question Hugo 🙂

This time I am able to grab the points :-P....

Last time I forgot to see the Go batch separator and comments and clicked on wrong one!!!

Thanks!!

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

Great question hugo. Last week i asked a question related to this kind of scenario. 🙂

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 3

Awesome, thank you for posting.

(first thing I noticed this time was the missing of "EXISTS" in trigger and then "= 1" condition, that made it easy to mark the correct choice after running the code couple of time in the head)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 4

Nice QotD!

Thanks

IgorMi

Igor Micev,My blog: www.igormicev.com

TomThomson SSC Guru Points: 104773 More actions · Answer 5

Good question and explanation.

Triggers that work only on single rows are a common problem, so anything that points out the pitfalls and warns people not to write them is a good thing.

But I have a minor quibble: if this is a business rule, it should be enforced by the schema if possible, not by code; and this is a classic example of a rule that can be enforced by a check constraint.

Tom

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

L' Eomot Inversé (4/25/2013)
Good question and explanation.
Triggers that work only on single rows are a common problem, so anything that points out the pitfalls and warns people not to write them is a good thing.
But I have a minor quibble: if this is a business rule, it should be enforced by the schema if possible, not by code; and this is a classic example of a rule that can be enforced by a check constraint.

+1 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 7

L' Eomot Inversé (4/25/2013)
But I have a minor quibble: if this is a business rule, it should be enforced by the schema if possible, not by code; and this is a classic example of a rule that can be enforced by a check constraint.

For a production implementation, I fully agree that a trigger, even a well-coded one, would be a terrible way to implement this rule.

But for a QotD about triggers, using a CHECK constraint would not have been a very smart choice! 😉

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

mtassin SSC-Insane Points: 23099 More actions · Answer 8

I needs my coffee... for some reason I was reading the question as... how many rows will be returned... and I saw the count(*) and went... 1 duh.... there's no group by...

apparently reading is something I completely forgot... *sigh* *watches pot brewing*

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

sestell1 SSChampion Points: 10230 More actions · Answer 9

I was surprised this was your question Hugo. I didn't think it was yours when I saw the Count(*) in the trigger! 😉

Lon-860191 SSCrazy Points: 2449 More actions · Answer 10

Great question, another correction to allow it to work is changing "= 1" to "> 0", obviously without the quotes included.

Thanks,

Lon

DBA by default Mr or Mrs. 500 Points: 522 More actions · Answer 11

Good question. The code was easier to follow than most lately. Thanks.

Miles Neale SSChampion Points: 13147 More actions · Answer 12

Miles Neale

SSChampion

Points: 13147

April 25, 2013 at 9:30 am

#1610003

Nice +1.

Not all gray hairs are Dinosaurs!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Thanks Hugo.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events