• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

T-SQL syntax to avoid firing a trigger?

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
dgloveruk
Asked:
dgloveruk
  • 2
2 Solutions
 
BrandonGalderisiCommented:
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
 
dgloverukAuthor Commented:
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
 
momi_sabagCommented:
there is no way to avoid firing a trigger
the only possible of bypassing the trigger is by using code inside the trigger
0
 
dgloverukAuthor Commented:
Thank you guys!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now