Solved

T-SQL syntax to avoid firing a trigger?

Posted on 2008-10-22
4
443 Views
Last Modified: 2012-05-05
Hi there,
I have a field that when it is updated causes a trigger which fires off an email to warn a department of a modification that was made to a field.
However, the department concerned who have the final word also make a change to this field and therefore do not need an email when they perform the change.
Is there any stored procedure syntax which would prevent the 'update trigger'.  This would be my preferred solution.  

However if this cannot be done, how can I test the user account that is being used to update the field so that I can check for this when the trigger fires?
0
Comment
Question by:dgloveruk
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 350 total points
ID: 22776469
You can use the current_user() function inside your trigger and if the user is a particular one, return.


http://msdn.microsoft.com/en-us/library/aa258248(SQL.80).aspx

create trigger tr_Update on MyTble

for update

as

if @@rowcount=0 

  return
 

set nocount on

if current_user() = 'SomeUser'

  return
 

print 'do trigger code here'

Open in new window

0
 

Author Comment

by:dgloveruk
ID: 22776581
Thats great, are you aware of any way to execute the update in t-sql without firing the trigger at all?
If this isn't possible I'll go ahead and award points now.
Thanks in advance!
0
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 150 total points
ID: 22776863
there is no way to avoid firing a trigger
the only possible of bypassing the trigger is by using code inside the trigger
0
 

Author Closing Comment

by:dgloveruk
ID: 31508738
Thank you guys!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now