Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2016
    Posts
    19

    Answered: trigger that only fires when update NOT coming from application

    Hello,

    I'm wondering if there's a way to attach a trigger to a table that ONLY gets triggered when updates are made via anything other than the application.

    For example, if the application makes an update to the table, the trigger isn't fired. But if I make an update to the table via SSMS, the trigger does fire.

    This question was prompted from the following scenario:

    We have an application that inserts records into a table called ImportFiles. The ImportFiles table has auditing fields, one of which is ModifiedBy. The application passes the "modified by" user in the query to do the update, but we weren't seeing that modified user in the ModifiedBy field. Instead we were seeing the system user. The reason turned out to be that the table had a trigger attached to it that set the ModifiedBy user to the system user when an update was made. So the application would set the ModifiedBy user to the appropriate application user, but then because this constitutes a change, this set off the trigger and the trigger set the ModifiedBy field to the system user.

    I took out the trigger and it fixed the problem. But I'm now also noticing that any updates I do via SSMS don't record me (as an SSMS user) as the ModifiedBy user.

    So now I'm wondering if it's possible to put that trigger back but only have it activated when make changes through a means other than the application.

    Is this possible?

  2. Best Answer
    Posted by MCrowley

    "We've been seeing a lot of trigger questions of late....

    Have a look at the PROGRAM_NAME() function, to see if it records the right application name in the trigger."


  3. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    We've been seeing a lot of trigger questions of late....

    Have a look at the PROGRAM_NAME() function, to see if it records the right application name in the trigger.

  4. #3
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    or have it fire when current sqlid not equal your system id?

  5. #4
    Join Date
    Dec 2016
    Posts
    19
    Quote Originally Posted by MCrowley View Post
    We've been seeing a lot of trigger questions of late....

    Have a look at the PROGRAM_NAME() function, to see if it records the right application name in the trigger.
    Thanks MCrowley. That worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •