November 1, 2012 at 1:02 pm
Hello,
I'm new to SQL, specifically T-SQL, I've done some research online and have a general idea of triggers. But I wanted to ask the community for any really good sites that will give good information on triggers, how they work, their capabilities, and examples.
Thank you to all who read and respond to this question. You assistants helps to create the next generation of guru's
David92595
November 1, 2012 at 1:06 pm
David92595 (11/1/2012)
Hello,I'm new to SQL, specifically T-SQL, I've done some research online and have a general idea of triggers. But I wanted to ask the community for any really good sites that will give good information on triggers, how they work, their capabilities, and examples.
Thank you to all who read and respond to this question. You assistants helps to create the next generation of guru's
David92595
BOL will do a far better job explaining this than anybody else. http://msdn.microsoft.com/en-us/library/ms189799.aspx
Triggers can be useful but be careful because they also be a real problem. They need to be set based to handle multiple row dml statements. Often times there are other constructs besides triggers that can accomplish some tasks more efficiently than triggers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2012 at 1:53 pm
It is probably best if you avoid triggers completely until you have a lot of experience with SQL Server.
November 1, 2012 at 2:04 pm
I concur with Michael and Sean. Triggers can be useful when properly designed, but are more often misused leading to performance problems, permissions issues, and in one case I recall vividly, deadlocks all over the place because all of the triggers were trying to write to the same denormalized table. Best to stay away from them until you have a better handle on SQL Server in general.
November 1, 2012 at 2:04 pm
Michael Valentine Jones (11/1/2012)
It is probably best if you avoid triggers completely until you have a lot of experience with SQL Server.
i would tend to disagree. you need to avoid triggers in production until you have confidence through testing that they will behave correctly (make sure you code them to handle multiple rows) and will not have an overly large performance hit that becomes a problem.
When i was new to sql after i had SP's and was starting on UDF's i also worked with triggers. if designed properly and tested thoroughly there is no problem with people with little experience with sql using triggers. the problem comes in when some one new to sql impliments a poorly designed poorly tested trigger that screws things up.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 1, 2012 at 3:09 pm
capnhector (11/1/2012)
Michael Valentine Jones (11/1/2012)
It is probably best if you avoid triggers completely until you have a lot of experience with SQL Server.i would tend to disagree. you need to avoid triggers in production until you have confidence through testing that they will behave correctly (make sure you code them to handle multiple rows) and will not have an overly large performance hit that becomes a problem.
When i was new to sql after i had SP's and was starting on UDF's i also worked with triggers. if designed properly and tested thoroughly there is no problem with people with little experience with sql using triggers. the problem comes in when some one new to sql impliments a poorly designed poorly tested trigger that screws things up.
You disagreed with me, and went on to make the case that someone who doesn't know what they are doing is the one who will screw things up with a trigger.
In other words, someone without a lot of experience with SQL Server.
I have fixed a lot of triggers over the years, and I can tell you from experience that people that are new to SQL Server are way more likely to screw things up with a trigger, and way more likely to decide to use a trigger because they don’t understand the implications of what a trigger does.
November 1, 2012 at 3:20 pm
capnhector (11/1/2012)
Michael Valentine Jones (11/1/2012)
It is probably best if you avoid triggers completely until you have a lot of experience with SQL Server.
if designed properly and tested thoroughly there is no problem with people with little experience with sql using triggers.
This is pretty much a catch 22 situation. If designed properly and tested thoroughly, there is no problem with using trigger, I completely agree. The problem is that inexperienced sql users are very unlikely to design the triggers properly. In my career, I've seen more performance, transaction and locking problems related to triggers than pretty much any other SQL Server construct.
November 1, 2012 at 3:35 pm
George M Parker (11/1/2012)
capnhector (11/1/2012)
Michael Valentine Jones (11/1/2012)
It is probably best if you avoid triggers completely until you have a lot of experience with SQL Server.if designed properly and tested thoroughly there is no problem with people with little experience with sql using triggers.
This is pretty much a catch 22 situation. If designed properly and tested thoroughly, there is no problem with using trigger, I completely agree. The problem is that inexperienced sql users are very unlikely to design the triggers properly. In my career, I've seen more performance, transaction and locking problems related to triggers than pretty much any other SQL Server construct.
its true that inexperienced sql users are more likely to mess up a trigger i just thought the blanket avoid them was a little to broad. i know some one who is very new to sql but very experienced in application development who understands load testing code, for him being new to sql but knowing how to design tests on applications i would be relativity confident if a trigger was called for he could design a very good one.
personally i like to avoid triggers and handle the tasks in the code of the application (whether through SP's or pick your method) but there are times to use a trigger. the problem i see with trigger use is they tend to be over used when better code could avoid them and perform every thing in a more efficient manor.
there are however times when through having to add new functionality or the need for auditing that a trigger is necessary. in those cases an accidental dba who is new to sql will probably need help learning how to design triggers even though he has little experience.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 1, 2012 at 4:37 pm
okay, so for starters I'm not brand brand new to SQL. Maybe I should have been a bit more clear about that. I've got about a year of SQL under my belt, compared to many of you I thought I was still new...
I took our database from an access database to a SQL back end and access front end. We are already using basic triggers utilizing the deleted table to make sure any accidental deletions that happen in our DB are saved. Though I did have some help from a friend setting it up initially.
I now also have more information on what exactly I'm need to do. When Field A is filled in with a date (all these will be date fields) I need to set Fields B,C, and D to auto fill with dates that are, for example 14 days from the date entered in field A. Field C to be filled in with 28 days after field A, ect.
I absolutely know to use a testing database, and have learned that the hard way over the months...
Primarily, I am looking for an example to base my trigger on. Both within the same table and in a different table (all within the same database).
Please let me know if you have any further questions that I may be able to answer.
Thanks again everyone,
David92595
November 1, 2012 at 4:46 pm
David92595 (11/1/2012)
okay, so for starters I'm not brand brand new to SQL. Maybe I should have been a bit more clear about that. I've got about a year of SQL under my belt, compared to many of you I thought I was still new...I took our database from an access database to a SQL back end and access front end. We are already using basic triggers utilizing the deleted table to make sure any accidental deletions that happen in our DB are saved. Though I did have some help from a friend setting it up initially.
I now also have more information on what exactly I'm need to do. When Field A is filled in with a date (all these will be date fields) I need to set Fields B,C, and D to auto fill with dates that are, for example 14 days from the date entered in field A. Field C to be filled in with 28 days after field A, ect.
I absolutely know to use a testing database, and have learned that the hard way over the months...
Primarily, I am looking for an example to base my trigger on. Both within the same table and in a different table (all within the same database).
Please let me know if you have any further questions that I may be able to answer.
Thanks again everyone,
David92595
If the B, C, D columns are constant based on column A, then you might want to consider the use of persisted computed columns, instead of triggers.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2012 at 4:48 pm
David92595 (11/1/2012)
okay, so for starters I'm not brand brand new to SQL. Maybe I should have been a bit more clear about that. I've got about a year of SQL under my belt, compared to many of you I thought I was still new...I took our database from an access database to a SQL back end and access front end. We are already using basic triggers utilizing the deleted table to make sure any accidental deletions that happen in our DB are saved. Though I did have some help from a friend setting it up initially.
I now also have more information on what exactly I'm need to do. When Field A is filled in with a date (all these will be date fields) I need to set Fields B,C, and D to auto fill with dates that are, for example 14 days from the date entered in field A. Field C to be filled in with 28 days after field A, ect.
I absolutely know to use a testing database, and have learned that the hard way over the months...
Primarily, I am looking for an example to base my trigger on. Both within the same table and in a different table (all within the same database).
Please let me know if you have any further questions that I may be able to answer.
Thanks again everyone,
David92595
If the columns are in the same table you can use computed columns. not sure about in other tables but this is one of those areas where a trigger would be a bad idea. an example of a computed column would be as follows:
CREATE TABLE compcolumn (
MyDate DATETIME,
MyComputedColmn AS (DATEADD(DD,14,MyDate)) PERSISTED
);
WITH cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)
INSERT INTO compcolumn
SELECT top 10 DATEADD(DD,N,GETDATE()) FROM cteTally
SELECT * FROM compcolumn
DROP TABLE compcolumn
the PERSISTED actually stores the value of the computed column allowing an index to be created on the column.
EDIT Jeff beat me to it.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 1, 2012 at 4:57 pm
Can Fields B, C, and D still be manually changed after the fact if need be? or are fields B,C, and D locked as computer values at that point?
November 1, 2012 at 5:04 pm
David92595 (11/1/2012)
Can Fields B, C, and D still be manually changed after the fact if need be? or are fields B,C, and D locked as computer values at that point?
With computed columns they are locked in. what you could do though is in your application code use something like the following:
CREATE TABLE compcolumn (
MyDate DATETIME,
MySemiComputedColumn DATETIME
);
INSERT INTO compcolumn (MyDate,MySemiComputedColumn)
SELECT '2012-11-01', DATEADD(DD,14,'2012-11-01')
SELECT * FROM compcolumn
DROP TABLE compcolumn
then you could update the column later if you needed and still not need a trigger.
as you can see i try my best to avoid triggers because they can create tuning nightmares.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 2, 2012 at 5:03 pm
capnhector (11/1/2012)
David92595 (11/1/2012)
Can Fields B, C, and D still be manually changed after the fact if need be? or are fields B,C, and D locked as computer values at that point?With computed columns they are locked in.
No, computed columns will automatically be recomputed to reflect any change in any column value used to compute the column.
That is, if columnB is based on an adjustment to columnA, and you change columnA, SQL will automatically change the value of columnB.
I would urge you not to PERSIST the column unless you really need to (like you expect to need to index it, which doesn't seem likely here, since you could just index columnA), since typically I/O is much more expensive than a tiny bit of CPU which is needed ONLY when the column is actually referenced in the query.
When Field A is filled in with a date (all these will be date fields) I need to set Fields B,C, and D to auto fill with dates that are, for example 14 days from the date entered in field A. Field C to be filled in with 28 days after field A, ect.
This is the perfect situation for computed columns IF you want the other columns to be automatically recomputed when columnA changes. Computed columns, especially if they are not persisted, will be vastly more efficient than a trigger.
ALTER TABLE dbo.your_table_name
ADD
columnB AS DATEADD(DAY, 14, columnA),
columnC AS DATEADD(DAY, 28, columnA)
--,...
If columnA is set to Nov 2, then columnB will be Nov 16. If you UPDATE columnA to Nov 5, then columnB will become Nov 19, whether persisted or not.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply