| Author |
Topic |
|
ruan4u
Posting Yak Master
132 Posts |
 Posted - 2004-12-06 : 16:31:27
|
| i have a payments table and a transaction table.i have triggers in payment table that inserts in transaction table.But before the insert i do some selects to check the status of the payment(this is in another table) and then do an insert inot the transaction table. Now will i run into any problems? can i have exception routines in triggers and if so how?Moreover these tables wil have million or more rows too.Any tips,r most welcome |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-12-06 : 16:34:32
|
| Can you post the DDL?Also can you post the business rules for the audits?Brett8-) |
 |
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-06 : 16:51:53
|
| insert into ledger(columns) select values from inserted as ins,invoice as inv,invoicepreferences as ip where ins.pk=inv.lnkins and inv.pk=ip.lnk set @er=@@error if @er <>0 insert into errorlog values('Payment Insert trigger err-1' ) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-12-06 : 21:13:51
|
for starters, instead of doing this within the trigger, create an sp and call that in your trigger  --------------------keeping it simple... |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-07 : 09:07:10
|
| Ok.now will it affect anything. From VB i am setting a begin trans and inserting into payments.One payment insert i have the above trigger to insert inot ledger.When i move it to a SP, do i need to add transaction? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 09:32:53
|
| You should avoid using the application layer to handle data transactions. The only time an application should start a database transaction is if there is a process completely external to the database that the database cannot control itself, but must be part of the transaction. If you're only working with data, write a stored procedure to do the work, include the transaction in the procedure, and simply call that procedure from your VB app. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-07 : 09:39:54
|
| yes agreed, but ihave header and detail tables in 2 diff Recordset.i cant pass Rset to SP. so i have to loop for each detail recordset and insert inot the detail table and then insert the header table.Now if there is any error in any insert i would want the entire thing to rollback.so i have to in VB |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-12-07 : 09:43:31
|
| No, you can pass data to a stored procedure using comma separated (CSV) or other delimited values, or using XML, that contains an entire recordset. Once in the procedure it can be parsed and processed as one operation. This is not only faster, more secure, and more efficient but also probably easier than the code you have now. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2004-12-07 : 09:58:31
|
| i could have 100 detail lines or more and u think i should CSV it? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2004-12-07 : 16:37:28
|
| Just to answer your question concerning what would happen if there was an error in the trigger...The trigger is consider to be part of the original operation (e.g., Insert, Update, Delete). If anything fails in the trigger the entire operation rolls back, including the original Insert, Update or Delete.HTH=================================================================Hear the sledges with the bells - Silver bells! What a world of merriment their melody foretells! How they tinkle, tinkle, tinkle, In the icy air of night!While the stars that oversprinkle All the heavens, seem to twinkle With a crystalline delight; Keeping time, time, time, In a sort of Runic rhyme, To the tintinnabulation that so musically wellsFrom the bells, bells, bells, bells, Bells, bells, bellsFrom the jingling and the tinkling of the bells. Happy Holidays! |
|
|
|
|
Triggers on critical tables