| Author |
Topic |
|
ya3mro
Starting Member
37 Posts |
 Posted - 2006-05-22 : 02:18:39
|
| i am haapy to knmow this forum i think we have a good team of SQL developer that can help us ...my question ::i usually use Stored Procedure in my developeing and i also read more and more about Triggers and how can i use it specially if i am not using Stored Procedure .... but it confuse me that i am not see any use to using Triggers with the Stored Procedures ...So if my idea is right or not ??http://english.islamway.com |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2006-05-22 : 02:43:39
|
| What is your requirement? Triggers are normally used when, an any DML on the table you have to do some manupilations like application bussiness rules etc on the data modifed. Stored procedure can be used when, you want to perform a bunch of proccess at the reqular time then you create a stored procedure.If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them. |
 |
|
|
ya3mro
Starting Member
37 Posts |
Posted - 2006-05-22 : 03:08:32
|
| i mean if i use stored procedure i am not need to create Trigger on the table as i will make the same code in the Stored procedure .. for example in an INSERT SP for a table i can make a check on SP instead of write it in this table INSERT Trigger ...so i see the Beneifit of Stored Procedure only when using DML from Code Not from SPhttp://english.islamway.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 04:27:41
|
| The slight problem with an SProc is if there needs to be an import process now, or in the future, that wants to import directly into the table.We have some import data in our application and we just cursor round it and use our standard INSERT SProc to make the Insertion. Boy is that slow though ... but its future proof in that any changes we make to the SProc in the future for extra validation etc. will be used by the import routine too.It might be OK to have a trigger that ONLY does RaisError type stuff for invalid data, rather than doing yet-more-updates to the already-inserted-row for custom business rules etc. - that's the sort of stuff that the SProc should be IMO.Kristen |
|
|
|
ya3mro
Starting Member
37 Posts |
Posted - 2006-05-22 : 06:34:16
|
| First :: What's the mean of IMO ??---------and really i still have no Complete Answer of my question ??How I Came To Islam? http://english.islamway.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 07:13:19
|
| IMO = In My Opinion" i still have no Complete Answer of my question "I suggest you ask some additional, specific, questions about anything you are unsure ofKristen |
|
|
|
ya3mro
Starting Member
37 Posts |
Posted - 2006-05-22 : 08:49:10
|
| "I suggest you ask some additional, specific, questions about anything you are unsure of"u mean i just write un useful subject i really have this question and i want some professional one in SQL to answer it to me ??How I Came To Islam? http://english.islamway.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 09:07:16
|
| I'm happy to try to answer any questions you may have.I think I've already done that, but if it isn't clear please ask some additional questions, or tell us about the particular problem you are working on and then perhaps we can give guidance as to the ways you could approach the problem .Kristen |
|
|
|
ya3mro
Starting Member
37 Posts |
Posted - 2006-05-22 : 11:00:54
|
| ok thanx too much Kristen ::i will discuss what i want ::i read about Triggers and i know that it can be used when any DML is executed for example::if i have Table called testTable with columns ( id , name , age)so when i use C# to insert new row i will do ::sqlcommand cmd =new sqlcommand("insert into testtable(name,age) values (@name,@age) ",myConnection)with parameter values and connection i can execute this commandand in the table testTable i can make trigger for insert as ::CREATE TRIGGER checkexistanceON testTableFOR INSERTASIF (select count(*) from testtable where name =(select name from inserted )) > 0 BEGIN print 'this Name is exist before in out testtable' ROLLBACK END ELSE 'ok'print GOthis trigger will check if inserted name is exist or not ...(( All is done by Trigger ))but NOWif i use SProci will not make the Trigger and i will make this check in the SP before inserting the Rowall i write above is - as i think - obvious My question is ::If i can find any useful use of TRGGERS with SP at the same Time ??as i see that we can use trigger only if i won't use SProcso if my conception of that is RIGHT ot not ??i wish i can give u the right to way to understand my question ??and thanx to all for ur interestHow I Came To Islam? http://english.islamway.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 11:21:34
|
| Lets assume you use SProcs, and they do the validation for all of your Business Rules.So updating data with your SProc is very "safe" for the application, plus the SProc can pass back "nice messages" that the application can display to the end user.However, lets assume you also have some external processes that update the tables directly - perhaps an import process which runs overnight. That doesn't use the SProcs, so you may still want to have the Business Rules logic duplicated in Triggers to prevent ANYTHING mucking up the data.Triggers are also useful for copying changed data into Audit tables, and may be useful in setting an "Update Date and User" in the table (although you could obviously do that in your Sproc it might be important that DBA doing updates manually did not forget the Update Date columns].I think the main thing to try to avoid in a trigger is an Update of the original table - because the INSERT/UPDATE will have made an update, and then the Trigger will make a SECOND update, in that instance I think it is better to use an SProc - and, if necessary, to FORCE the Bulk Import routine to loop round the import data and call the Sproc [instead of just updating the tables directly](In your example you would be better to use RAISERROR rather than PRINT, but it may be a slightly pedantic point!)Kristen |
|
|
|
samuelclay
Yak Posting Veteran
71 Posts |
Posted - 2006-05-22 : 11:51:34
|
| As Kristen mentioned, triggers can be useful for monitoring data changes. I have had clients that had certain fields in their data where they wanted to track every change to that data, including date/time of the change and the old and new values. Now, the data can be manipulated in a variety of ways, stored procedures, inline sql code from web page, dts packages importing data, someone monkeying with data in Query Analyzer, etc.. By using triggers on the table, it is very easy to monitor the required fields and push the change information to an audit table, regardless of how the change takes place. To do it without using a trigger, every method of changing the data (except for QA, how could you enforce that?) would have to include this auditing code... And when the auditing requirements are changed, with triggers you can make the changes in one place, not throughout your application.And if the auditing comes into play with an existing application, it could be a nightmare to try to doing it without triggers.I guess my answer to your question would be that triggers and stored procedures have different primary uses (even if certain things could be handled by either) so use the code that works best for a given situation. In your example, where you roll back a transaction if a username exists, this works fine in the stored procedure, if one record is being entered and you wanted immediate feedback on whether it succeeded or not, but if you had an import running nightly, adding thousands of records, your strategy for checking and reporting on duplicates would need to be re-examined. |
|
|
|
ya3mro
Starting Member
37 Posts |
Posted - 2006-05-23 : 07:01:40
|
| Thanx too much my friends ::i really have new info ::1- Using Triggers is so important in IMPORT operation2- Triggers are also useful for copying changed data into Audit tables (( But i have no info about Audit tables ))3- DBA doing updates manually so trigger is so important in this case3- How I Came To Islam? http://english.islamway.com |
|
|
|
|
Stored procedure OR Trigger