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
 The Law of Triggers

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2011-08-12 : 09:28:18
The Law:
Triggers are always badly written, unless it is a re-write.

The Reason:
Triggers are very challenging to write, but good SQL developers who are up to the challenge avoid them like a rabid Rottweiler. Therefore, the only people that write them are developers who are not up to the challenge.


Just an observation after re-coding a trigger that was causing a 1 million row update to run for 30 minutes in SQL 2000 and about 16 hours in SQL 2008. With the new version of the trigger, the update finished in about 25 seconds, just about 5 seconds longer than with no trigger at all.





CODO ERGO SUM

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2011-08-12 : 09:31:57
Trigger Ergo Sum

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-08-12 : 09:52:26
I plead guilty. I sometimes invite the rabid Rottweiler in for auditing purposes.

http://www.youtube.com/watch?v=CZOeWFBy75A
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-08-12 : 10:45:54
"Triggers are very challenging to write, but good SQL developers who are up to the challenge avoid them like a rabid Rottweiler."

Absolutely disagree. I don't hesitate to implement a properly written trigger when it is the best solution to a problem. I'd rather put a data rule in a trigger than in a sproc.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-08-24 : 13:27:03
quote:
Originally posted by Michael Valentine Jones

The Law:
Triggers are always badly written, unless it is a re-write.

The Reason:
Triggers are very challenging to write, but good SQL developers who are up to the challenge avoid them like a rabid Rottweiler. Therefore, the only people that write them are developers who are not up to the challenge.
CODO ERGO SUM
I like it. I hope you don't mind if I use it. I will mention the source each time, I promise.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-08-24 : 13:44:06
"Triggers are very challenging to write, but good SQL developers who are up to the challenge avoid them like a rabid Rottweiler."

Tommy-rot.

Our triggers are written by code-generation-code, and are perfect. End of. !
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts
Posted - 2011-08-24 : 14:55:16
"Tommy-rot."


Such Language Kristen


Jim
Users <> Logic
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2011-08-24 : 15:22:33
I tend to implement much of the business logic into triggers.
The main reason is that we allow the developers to write any number of one-task application to work against the database, and not all them them are the brightest.
When we code much of the business rules in triggers, it doesn't matter which application that access the database, the data will be consistent. Even if the developers edit the data manually in ssms.

Last count gave some 70 applications working against same database.


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2011-08-24 : 17:42:12
quote:
Originally posted by JimL

"Tommy-rot."

Such Language Kristen

Apologies for offending you Jim
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-08-25 : 05:54:00
quote:
Originally posted by SwePeso

I tend to implement much of the business logic into triggers.
The main reason is that we allow the developers to write any number of one-task application to work against the database, and not all them them are the brightest.
When we code much of the business rules in triggers, it doesn't matter which application that access the database, the data will be consistent. Even if the developers edit the data manually in ssms.

Last count gave some 70 applications working against same database.


N 56°04'39.26"
E 12°55'05.63"
I have different approach. We give developers permissions to stored procedures and views, not to tables.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-08-25 : 09:34:49
quote:
Originally posted by mmarovic

quote:
Originally posted by SwePeso

I tend to implement much of the business logic into triggers.
The main reason is that we allow the developers to write any number of one-task application to work against the database, and not all them them are the brightest.
When we code much of the business rules in triggers, it doesn't matter which application that access the database, the data will be consistent. Even if the developers edit the data manually in ssms.

Last count gave some 70 applications working against same database.


N 56°04'39.26"
E 12°55'05.63"
I have different approach. We give developers permissions to stored procedures and views, not to tables.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Huh? Who writes your new database code if your developers can't access tables? Or do you not class database developers as developers here? Or do your DBA's do all your database development.

Assuming you mean -- all 'application layer' developers here???

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-08-25 : 11:42:30
Well, it depends on company, not each one has the same process. For example in Monster.com we had database coding strandards, templates and database engineers assigned to each project covering around 50 developers. We had tools extracting changes from version control system, db branch. Then we had strong code review policy. After we made a revision, developers had to apply changes we requested. After the release, we picked specific topic that needed enforcment and training and selected developers for additional training. In addition to that, we discussed which rules had to be added, modified or occassionally omitted from the database coding standards.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-08-25 : 11:49:21
About tables: In Monster developers accessed tables, but they had to put code in stored procedures. In a company producing billing system for telecom operators, it was database team that wrote stored procedure code, and developers could just execute stored procedurs or select data from views. However that was a process in division I was working for. In another division process was different, and the code quality was different as well.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-08-29 : 10:24:11
quote:
Originally posted by mmarovic
I have different approach. We give developers permissions to stored procedures and views, not to tables.

Uhm....how do you think restricting access to views avoids the violation of business rules not implemented by triggers?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts
Posted - 2011-08-29 : 15:34:34
quote:

Uhm....how do you think restricting access to views avoids the violation of business rules not implemented by triggers?

They can select data from views, for data modification they can just execute procedures. Actually, in most cases they also read data using procedures. Business logic is implemented by procedures.


Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts
Posted - 2011-08-29 : 16:36:47
OK. Your views are read-only then.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-08-29 : 22:58:23
[code]Create Trigger mvjTrigger
On MyTable -- every table
FOR INSERT, UPDATE, DELETE
AS

IF SUSER_NAME() = 'Domain\MVJ'
BEGIN
Rollback
RaisError ('Sorry Michael, we don''t care if you ARE an admin. Noooo don''t drop me.', 16, 1)
END[/code]
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2011-08-30 : 11:19:51
quote:
Originally posted by russell

Create Trigger mvjTrigger
On MyTable -- every table
FOR INSERT, UPDATE, DELETE
AS

IF SUSER_NAME() = 'Domain\MVJ'
BEGIN
	Rollback
	RaisError ('Sorry Michael, we don''t care if you ARE an admin.  Noooo don''t drop me.', 16, 1)
END



If only the triggers I get to deal with were so well written...








CODO ERGO SUM
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts
Posted - 2011-08-30 : 11:51:36
lol
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site