| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2008-10-05 : 04:04:09
|
| Hi,I've never used a trigger before, and have been doing some reading up on them. I have a few tables that I am considering creating triggers for that fire after an insert into the table. I only have 1 SPROC that inserts data into this table.Since I only have 1 SPROC that inserts, is there any benefit of using a Trigger ? Perhaps its actually faster to just put the trigger action in the SPROC that does the insert ? As I understand it, triggers are mostly helpful for situations where you want to handle an event that could be coming from anywhere ?Thanks for any tips!Mike123 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 04:11:40
|
| Trigger basically contains a piece of code that you want to execute whenever a certain triggering action (insert,update,delete) happens on a table. WHether or not you want a trigger depends on logic you want to perform and when you need them to be executed. If SQL 2005 or later, you can perform the trigger logic in your procedure itself using OUTPUT operator. But for ease of maintainability its better to keep logic in trigger itself.In your case, what are you trying to perform durting insert actions? will the sp be inserting on more than 1 table? Also use of trigger will always have an impact of performance. |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-10-05 : 04:17:06
|
| Hi Visakh15,I have about 6 individual tables. When these tables have a record inserted into them, I need to insert another record into a logging table. I'm not sure if I should do this via Trigger, or if I should modify SPROCS that insert into these tables, by adding a second insert statement.Thanks!Mike |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 04:43:45
|
quote:
Originally posted by mike123
Hi Visakh15,I have about 6 individual tables. When these tables have a record inserted into them, I need to insert another record into a logging table. I'm not sure if I should do this via Trigger, or if I should modify SPROCS that insert into these tables, by adding a second insert statement.Thanks!Mike
you need trigger on all 6 tables for that. Each insert action on each of table causes the trigger to fire and insert records onto logging table. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-10-05 : 04:48:31
|
| Personally I would incorporate the code into the SPs. I hate triggers - all this weird side effect stuff happen. Update your SPs and revoke update/insert rights on your tables |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-10-05 : 05:49:41
|
| Hi Visakh16,Ok yes I understand I would need a trigger on all 6 tables if I wanted to use triggers.I am questioning which way would be better for me ... The trigger way or what loztinSpace's suggests..Are there any pros/cons to each method anyone can list ?Thanks!Mike123 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-05 : 07:10:52
|
| It depends on how you want it to be done. If there are some really complex operations to be done after logging i would prefer to do them all in stored proc itself. Performance will be really bad if you try to do too complex things inside trigger.On the other had, if you consider maintainability, certainly trigger approach will be better as you will have explicit evidence of what all operations are defined on a table following an insert,delete or update. If you're putting the code in SP's people will find it difficult to track them when at a later change they need some modifications to be done on table and needs to find out dependent code which might be sitting on some other stored proc. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-10-05 : 10:48:58
|
Also, triggers are automatically made in the same transaction as the statement that triggers the trigger. If one fails, both are rolled back.If you have two insert statements in the SP, make sure both of them are wrapped in same transaction and write code to handle the error.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-10-06 : 04:11:00
|
| thanks for all the feedback !:) |
|
|
|
|
when to use a trigger