| Author |
Topic |
|
yeoryuel
Starting Member
9 Posts |
 Posted - 2009-02-25 : 11:52:05
|
Hi SQLers, Is there any possibility to run the Trigger with some conditions?? Situation:I have a form which gets value only 'A' or 'B' from the textbox.If it is 'A' then my trigger(PubTrigger) will be enabled else if 'B' my trigger should be disabled.Pls note: Everything should be in back end [SQL server 2005] and not in front end. And pls help as soon as possible i ve tried everything but being a newbie i couldnt just break through ! Advance Thanks !! Success Code: "Impress Yourself" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-25 : 12:02:42
|
| no need of toggling enabling and disabling, just write logic to do the processing only in case of value being 'A' |
 |
|
|
yeoryuel
Starting Member
9 Posts |
Posted - 2009-02-25 : 12:07:40
|
| Very very thanks Visakh !! Actually its the After trigger (DML),pls let me know where to write this case whether in the create trigger itself or ?? will you give me some example for that ,Success Code: "Impress Yourself" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-25 : 14:17:22
|
| Conditional processing (in the trigger) based on the value of your textbox will only work if that A/B column is in the table with the trigger. You definately don't want to disable the trigger because then concurrent sessions will bypass the trigger as well. If you want specific examples then post the table structure (post the generated CREATE script don't just type the column names). Also post your current trigger code. And say if the A/B textbox corresponds to one of the columns in your table.Be One with the OptimizerTG |
|
|
|
yeoryuel
Starting Member
9 Posts |
Posted - 2009-02-26 : 01:11:52
|
| Hi TG! I will give you the explanation.I have a textbox(with a default value 'A') and button in my front end and whereas in the back end,I have a table named mytbl which has columns [rownumber,usernam,clientid,spid and duration]. This table's records are automatically inserted by SQL PROFILER during normal condition as [1,ADMIN,77,45,233],[2,ADMIN,77,46,453],[3,ADMIN,77,47,504] and so on.... But when the textbox has some other value say 'B', my trigger should execute the update for the further normal insert as [1,ADMIN,77,45,233],[2,ADMIN,77,46,453],[3,ADMIN,77,47,504],(after change in textbox)[4,OTHERTEXT,77,48,682],[5,OTHERTEXT,77,49,721]Is it possible TG?? as i mentioned earlier everything must be in backend .... Advance Thanks TG !!Success Code: "Impress Yourself" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-26 : 10:22:57
|
| Are you saying that you are using SqlProfiler with "save to table" option? Then at certain times you want to be able to overwrite one of the column values (via a trigger) as new rows are being inserted?Be One with the OptimizerTG |
|
|
|
yeoryuel
Starting Member
9 Posts |
Posted - 2009-02-27 : 09:43:57
|
| Exactly TG! am using SQL profiler with save to table option.Yes i need to overwrite one of the column values(into textbox value) via a trigger as new rows are being inserted.. how to do that tg ?? you almost got me !!!Success Code: "Impress Yourself" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-27 : 11:00:58
|
>>how to do that tg ?? you almost got me !!!Most people that want a solution to a problem don't try to hide the details  I'm just trying to understand what your objective is. I almost understand... >> i need to overwrite one of the column values(into textbox value)Is the value you are modifying one of the profiler columns or did you add your own column to the target table? Is the new value that you want to write the same value that is in your custom application's, user entered TextBox or some constant value? Your original question stated that based on a value in the textBox you want to either overwrite an existing profiler column with a constant value or NOT overwrite the value.Bottom line is I don't think what you want to do is possible. There is no way that, from the scope of the trigger, you will be able to know the value of your application's TextBox. The trigger will only be aware of value of the columns that are in that table and/or being inserted. And since Profiler is generating the inserts rather than your own stored procedure you can't use [Context_Info()] or build in any other logic to replace the trigger.Why do you want to do this? What is the big-picture objective? Perhaps we can suggest an alternative approach.Be One with the OptimizerTG |
|
|
|
yeoryuel
Starting Member
9 Posts |
Posted - 2009-02-28 : 00:50:52
|
| thanks TG !! [so sorry for not to furnish necessary info...]>> i need to overwrite one of the column values...am not going to modify the profiler directly ! I am going to modify the target table !!! Then its a user entered value in the textbox not a constant value.............Why i want this is ....Its just a mini project(asp.net which has admin and guest login) to show my guide that who are all accessing database. But i must use SQL Profiler and asp.net... with the help of sql profiler, whatever i do in DB, i can able to track but its showing the name ADMIN in the NTUSERNAME column. what i need exactly is when i login as a guest .. the value which is going to enter as ADMIN will be modified as a guest name specified in the applcn. i must modify in the target table only.. IS it possible TG ??Success Code: "Impress Yourself" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-28 : 08:41:36
|
| No. But perhaps when [guest] is logged in - your application's SELECT of your target table can replace ADMIN with the value in your textBox. That way you don't need a trigger and you don't need to loose any info that profiler wrote. Your application has the value of textBox so it can conditionally do the REPLACE in a CASE statement.Be One with the OptimizerTG |
|
|
|
yeoryuel
Starting Member
9 Posts |
Posted - 2009-02-28 : 10:07:23
|
| "No">>>>>Why is it not possible TG?? any way the SQL PROFILER going to insert some records in that target table why cant we just enable the triggers (AFTER insert, update the same newly entered record["ADMIN"] as "ANONYMOUS"(textbox value not necessary))if i login as guest and disable the trigger when the admin is logged in????REPLACE, CASE>>>>>>>A Replace in a case statement ?? i tried but it yields replacement for all values in a table. Do u have any idea on that ??Success Code: "Impress Yourself" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-02-28 : 22:55:27
|
| Well I explained why the trigger wouldn't know the value of your text box. But now that you've changed your requirements to using a constant, it is possible. Is your application available to anyone else? In other words can someone logged in a guest and someone else logged in as admin give conflicting enable/disable states to the trigger? If an admin is accessing the database while you are logged in as admin do you care that their rows will also be modified?Anyway, look in books online for "enable trigger" and "disable trigger" (this syntax is very simple).Be One with the OptimizerTG |
|
|
|
yeoryuel
Starting Member
9 Posts |
Posted - 2009-03-01 : 10:11:39
|
| can someone logged in a guest and someone else logged in as admin give conflicting enable/disable states to the trigger>>>>>>>>>>>>>>>>>>>>>> no the application is available to me only and not anyone else[so there is no possibility of simultaneous guest and admin logins].......and it doesnt have conflicting enable/disable states to a trigger........but when i use the triggers it modifies entire row as constant to all the values which has 'ADMIN' valueSuccess Code: "Impress Yourself" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-03-02 : 11:02:20
|
| >>it modifies entire row as constant to all the values which has 'ADMIN' valueNot sure what you mean. Your UPDATE statement should only be SETing the column you want to update. post your trigger code.Be One with the OptimizerTG |
|
|
|
|
Conditional Triggers ? Urgent.