| Author |
Topic |
|
guddi1
Starting Member
4 Posts |
 Posted - 2007-07-02 : 22:21:52
|
| Hi , I am new to using triggers. I am creating a trigger that updates a table called Membership when a specific column in the table is updated. When this specific column is updated, the trigger starts to do its job of updating other information in the membership table. Now, my question is what is best practice in updating the base table, in this case, Membership table. Can I directly update the info that needs to updated in the Membership table from inside the trigger. Or, is there an indirect way that is preferred?Thank You, Trigger Newbie |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-02 : 22:52:24
|
| The best practice is to avoid Triggers. Channellize your INSERTs/UPDATEs through stored procedures and update the other table when the update on the Membership table happens.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-07-05 : 00:16:39
|
| Seconded. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-07-05 : 02:02:23
|
I think of triggers as a purposeful introduction of side-effects into the database code. generally, in programming you want to avoid side-effects because they cause unexpected things to happen (from the point of view of a maintenance programmer, especially).if you have a function called GetValue() would you be surprised if, in addition to getting a value, as a side-effect it also happened to send an email to sign you up for a book of the month club? this is an extreme example, but the idea is the same.
elsasoft.org |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-05 : 03:21:36
|
I don't agree best practice is to avoid triggers. Best practice is to use them when you need to and don't use them when you don't. It is worth noting that they do appear to be overused, especially by people first getting to grips with database design.quote:
Originally posted by guddi1
I am new to using triggers. I am creating a trigger that updates a table called Membership when a specific column in the table is updated. When this specific column is updated, the trigger starts to do its job of updating other information in the membership table. Now, my question is what is best practice in updating the base table, in this case, Membership table. Can I directly update the info that needs to updated in the Membership table from inside the trigger. Or, is there an indirect way that is preferred?
This sounds like a violation of third normal form. Are you familiar with this concept? What are the columns you want to update? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-05 : 03:24:55
|
| Also Nigel has started adding some stuff on triggers to his site:http://www.nigelrivett.net/#TriggersNote - There are some typos and at least one error that I spotted but it is a nice handling of the topic. Books Online is useful too of course.I would still consult with the people here though before you start coding as you may find that triggers are innappropriate in this instance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-05 : 06:27:32
|
"Can I directly update the info that needs to updated in the Membership table from inside the trigger"Yes, a [normal, "After"] trigger fires AFTER the data has been inserted into the Membership table. So:1) You can update the Membership record (a second time, obviosuly inefficient!)2) The original data that is stored int eh membership table has to be valid - e.g. if there is a constraint that you plan to ONLY provide via the Trigger then your initial Insert will fail  3) The initial insert to the Membership table and the subsequent Update of it in your trigger are in a single transaction, so the data will appear consistent to queries (well, assuming they don't use Dirty Reads I suppose!)Note that a Trigger needs to be able to handle Multiple rows, not just a single row.CREATE TRIGGER MyTrigger ON dbo.MyTableFOR INSERT, UPDATE, DELETE -- Include any that apply for THIS triggerAS UPDATE U SET MyCol1 = SomeValue FROM dbo.MyTable AS U JOIN inserted AS I ON I.MyPK = U.MyPKGO Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-05 : 06:49:21
|
@OP - You'll need a check that the particular column has change too  |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-07-05 : 11:10:56
|
quote:
Originally posted by dinakar
The best practice is to avoid Triggers. Channellize your INSERTs/UPDATEs through stored procedures and update the other table when the update on the Membership table happens.
Absolutely horrible advice.The best practice is to use triggers intelligently.e4 d5 xd5 Nf6 |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-05 : 12:55:38
|
quote:
Originally posted by blindman
quote:
Originally posted by dinakar
The best practice is to avoid Triggers. Channellize your INSERTs/UPDATEs through stored procedures and update the other table when the update on the Membership table happens.
Absolutely horrible advice.The best practice is to use triggers intelligently.e4 d5 xd5 Nf6
And how many people do that? My advise was relative to the questions we see here. someone with years of experience or at least has read some good articles would have known that already. And for the purpose mentioned in the original question I would definetely NOT use a trigger.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-07-05 : 23:28:50
|
quote:
Originally posted by dinakarMy advise was relative to the questions we see here. someone with years of experience or at least has read some good articles would have known that already. And for the purpose mentioned in the original question I would definetely NOT use a trigger.
Another irresponsible blanket statement. The poster wants to update some columns in a table any time the value in another column changes. Perhaps he is implementing versioning or data auditing. This is exactly what triggers SHOULD be used for.e4 d5 xd5 Nf6 |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-05 : 23:50:40
|
quote:
Originally posted by blindman
quote:
Originally posted by dinakarMy advise was relative to the questions we see here. someone with years of experience or at least has read some good articles would have known that already. And for the purpose mentioned in the original question I would definetely NOT use a trigger.
Another irresponsible blanket statement. The poster wants to update some columns in a table any time the value in another column changes. Perhaps he is implementing versioning or data auditing. This is exactly what triggers SHOULD be used for.e4 d5 xd5 Nf6
Or perhaps not. which is not clear from the post. Based on the information provided, if he just wants to update information in TableB when info in tableA is updated I would advise doing it in the proc. My theory is to ALWAYS use stored procs for any data querying/manipulation. Which is what I meant by channeling the INSERTs through procs. Now you might (and perhaps will) argue that we can use in-line SQL too. which is fine. that doesnt make my argument wrong or irreponsible or rubbish. And the reason I recommend procs for INSERTs is for easier maintenance. If there are any schema changes in future it is easier to trace back to the procs vs making changes in procs as well as application code.Having said that, my recommendation was to use the same procs to modify TableB also when updates to TableA are made. I dont see the need for any triggers in this scenario. Now if you are talking about auditing/versioning then yes Triggers would be used. This is not clear from the OP's question. Just as you assumed he is asking for auditing/versioning I assumed otherwise. Now, if you diagree with what I said I would appreciate it if you posted your counter-arguments rather than bad mouthing and calling my posts horrible/irresponsible. I dont think any piece of advice is horrible/irresponsible given the parameters in the original question. This is an open forum and everyone is entitle to their opinion. It would be more professional of you if you respected other members too.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-06 : 04:54:03
|
quote:
Originally posted by dinakar
Or perhaps not. which is not clear from the post. Based on the information provided, if he just wants to update information in TableB when info in tableA is updated I would advise doing it in the proc. My theory is to ALWAYS use stored procs for any data querying/manipulation. Which is what I meant by channeling the INSERTs through procs. Now you might (and perhaps will) argue that we can use in-line SQL too. which is fine. that doesnt make my argument wrong or irreponsible or rubbish. And the reason I recommend procs for INSERTs is for easier maintenance. If there are any schema changes in future it is easier to trace back to the procs vs making changes in procs as well as application code.Having said that, my recommendation was to use the same procs to modify TableB also when updates to TableA are made. I dont see the need for any triggers in this scenario. Now if you are talking about auditing/versioning then yes Triggers would be used. This is not clear from the OP's question. Just as you assumed he is asking for auditing/versioning I assumed otherwise.
The problem I think Dinakar was your answer was unqualified and not related to the question. Your post actually says "don't use triggers period" not "probably not recommended in this instance".If you think blindman will recommend in-line SQL you don't know him at all I see this sproc argument trotted out regularly and it does really surprise me. To start - no application of mine accesses tables directly. Everything is via sprocs. But if some data logic needs applying to a table everytime that the data is changed without exception you cannot entrust this to a sproc. What happens when you have to perform administrative functions? Maybe a data load from file, adjustments to the existing data, deletions\ archiving of old data? Depending on the circumstances, you may not be able to afford for the rules not to be applied. You can enforce planned, application access via stored procedures but not all table access. So a trigger is the way to go. Admittedly they are not totally transparent and can be the source of bugs but they are more fool proof than entrusting all DBAs\ developers to incorporate all the relevent rules into every ad hoc statement they write and so on balance can be the appropriate choice, depending on the circumstances.In any event I still think there may be a third normal form problem here EDIT - added a word I missed out |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-06 : 06:49:21
|
| There is also the performance issue of having to call an SProc on each row in turn during a bulk load.If you only bulk load a database once its OK, but it still a significant slow-down, and in my experience comes at a stage when time is of the essence!For issues where we want to enforce something - such as a calculated field - we use triggers in development to make sure the rules are obeyed, and then remove the triggers for performance on Production. We can leave the in place until after any initial Bulk Load, so we can double check that any calculated fields / constraints are honoured correctly, and thereafter we route most DML stuff through Sprocs without Triggers (audit trail is the only major exception)Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-06 : 06:57:45
|
| Probably worth noting - everything I produce is used in house. As such there may be modifications made outside of an application (as it happens 95% of the stuff I produce doesn't even have an FE however there is a sproc API for all routine functions). If you are producing something for a client and they should not be modifyting data directly then fair point - it is a different consideration. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-06 : 07:01:21
|
| "they should not be modifyting data directly ..."... but they may need an automated data-import routine, which comes back to how to handle bulk imports again.Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-07-06 : 10:13:17
|
quote:
Originally posted by dinakarOr perhaps not. which is not clear from the post. Based on the information provided, if he just wants to update information in TableB when info in tableA is updated I would advise doing it in the proc.
Please read the post:quote:
Originally posted by guddi1
...I am creating a trigger that updates a table called Membership when a specific column in the table is updated. When this specific column is updated, the trigger starts to do its job of updating other information in the membership table...
e4 d5 xd5 Nf6 |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-07-06 : 17:47:30
|
| Almost all the scenarios mentioned by pootle_flump, Kristen are at a level higher than the normal, insert here update there, update here update there types. For the question asked by OP, info gets updated in TableA and some updates need to happen in TableB, do you recommend Triggers for a task as simple as this? What if there batch updates or batch inserts? Now there is no mention of auditing/versioning. The tasks metioned *could* be considered auditing or even not too. There was absolutely no need to flame someone for suggesting not to use Triggers and use procs instead, which I assert again, is good enough advice for the task mentioned. Personally I consider triggers evil. I have used triggers and I know how much of a maintenance nightmare they can be. And I ended up removing them. My scenarios was mostly for calculation purposes and enforcing business logic across tables and that too about 4-5 yrs back. Now I avoid triggers if I can. You can come back and say I dont know how to use triggers or have not used them wisely. I did my homework, then, before using them and getting rid of them was the best thing.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2007-07-06 : 18:05:58
|
| IME, you cannot expect to create any moderately complicated business database without triggers. Database constraint management in SQL Server is restricted to FK's which is only the tip of the iceberg for business rules.Triggers allow you to enforce transition constraints.In saying that, I do try and severely limit using triggers for procedural actions...DavidMProduction is just another testing cycle |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-07 : 04:54:39
|
| Dinaker - that response leaves me at a bit of a loss. I think this is probably just best left. The only two things I will say (which are nothing new to the thread) are:1) You still haven't read the OPs requirements properly given that you have summarised them incorrectly on two occasions now.2) "The best practice is to avoid Triggers" is a generalisation not targeted at the OPs specific requirements and strongly implies some industry wide held standard which is nonexistent. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-07 : 04:55:41
|
quote:
Originally posted by byrmol
Database constraint management in SQL Server is restricted to FK's which is only the tip of the iceberg for business rules.
You need the buggers merely to enforce full RI let alone business rules |
|
|
|
Next Page
|
Trigger best practices