title: How to pass external parameters to triggers in sql?
body: <p>Is it possible to write a trigger which accepts external parameters?</p>
  
  <p>While storing the history data, I want to store the Application User Name who has done the Update operation. I thought to use <code>User_Name()</code> but came to know that, it only returns the database user, NOT the application user.</p>
  
  <p>Therefore, I am looking for some suggestions on creating triggers with external parameters so that I can pass the application user's id to this trigger. Think I have clearly explained my needs. Thanks in advance !</p>
  
  <p>Here is my code what i have tried....</p>
  
  <pre><code>ALTER TRIGGER [dbo].[Audit]
  ON [dbo].[MIS_Opus] FOR UPDATE  
  AS  
  BEGIN  
  SET NOCOUNT ON;  
  DECLARE @d DATETIME = GETDATE(), @u SYSNAME = SUSER_SNAME();  
  Insert into dbo.MIS_OpusAuditTrigger  
  (  
  OpusId, Category, ProcessName, DOP, RampupCode, _Count, TimeTaken, Remarks, Audit_Action,    Audit_Timestamp, Changed_By  
  )  
  SELECT   
         OpusId,  
         Category,  
         ProcessName,  
         DOP,   
         RampupCode,   
         _Count,   
         TimeTaken,  
         Remarks,  
         'before update',  
         @d,  
         @u  
      FROM deleted -- &lt;--- ****** deleted ******   
    UNION ALL   
    SELECT   
        OpusId,  
         Category,   
         ProcessName,   
         DOP,  
         RampupCode,  
         _Count,   
         TimeTaken,   
         Remarks,   
         'After update',   
         @d,   
         @u   
      FROM inserted;  
  END  
  </code></pre>
  
  <p>In the above code the database user id only stored i need to store the user's id who did update operation. I am using MSSQL server 2012 Please tell how to do it...</p>