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

Different trigger flow according to which user is performing the INSERT/UPDATE

Ok, this is a little trickier than it sounds.

Basically, if someone is inserting/updating records without passing through the framework - for example just connecting to the database - I want to perform PATH A; but if someone is logged in on the website then I want to perform PATH B.

When someone is logged in on the website and performs any database activity, he will have an active session_token in a table, which will also help me to identify the username, his IP and the aforementiond session id. My framework will pass this session_token as a paramenter for every stored procedure I call.

So, how can I check, during a trigger, if the stored procedure that caused the insert/update has a given parameter?
0
Emanuele_Ciriachi
Asked:
Emanuele_Ciriachi
  • 4
  • 4
  • 2
1 Solution
 
BrandonGalderisiCommented:
>> So, how can I check, during a trigger, if the stored procedure that caused the insert/update has a given parameter?

You can't unless the token is written to the table.  You can however do logic in the trigger using the user_name() or SUSER_SNAME() functions to determine the SQL(/windows) login used and base it on that.  But that will only work if you application has a set credential (which it should).
0
 
Emanuele_CiriachiAuthor Commented:
My application has a set credential, but the SQL Server users performing operations on the db do not correspond to the users of my application -  more users of the same role will perform operations as the same SQL Server user, and the only way to find who she is is looking up his user ID in the session table, using the session key passed as parameter as the key.

I am thinking of writing a T-SQL function, to invoke into every stored procedure that requires authentication, that will use this session key to identify the user and store his user ID... somewhere, so that follow-up triggers will know the user by retrieving this information from some known place.

Do you think this might work?
0
 
BrandonGalderisiCommented:
It will only work if that value (whether it be the token or the user_id) is recorded in the table.  Triggers won't know what other variables may have been set in the code executing that caused the trigger to fire.  They can only see locally declared variables and the results of the inserted/deleted tables.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Emanuele_CiriachiAuthor Commented:
Indeed. Is there an optimal way of achieving this? I mean a solution that will both preserve efficiency (which could be jeopardised by excessive usage of locks) and reliability (*NEVER* should someone's storeproc be run on someone else's credentials).

I don't know too much about concurrency in SQL Server - but I can tell you that the same user will only run a storeproc at a time. Is there some user-specific session variables of sort in T-SQL?
0
 
Anthony PerkinsCommented:
One cheesy approach you could take is use the HOST_NAME() system function to know the calling program.  That would of course assume you can modify the connection string for both applications or at the very least one of them.
0
 
Emanuele_CiriachiAuthor Commented:
No thanks, I want an industry-strength solution that will work on a per-user basis and guarantee consistency - no cheesy approaches here.
0
 
Anthony PerkinsCommented:
Fair enough.

Good luck.
0
 
BrandonGalderisiCommented:
>>*NEVER* should someone's storeproc be run on someone else's credentials

But that doesn't mean that the more than one user wouldn't be executing the same procedure at the same time.


>> Is there some user-specific session variables of sort in T-SQL?

No.  But you can use a #temp table in the calling code which can be conditionally used to look up the value in the trigger code.
CREATE TABLE HelloWorld
     (id       INT IDENTITY
     ,a        INT
     ,b        INT
     ,userId   INT
     )
GO
CREATE TRIGGER trg_HelloWorld_test
ON HelloWorld
FOR INSERT,UPDATE,DELETE
AS
IF @@rowcount = 0
     RETURN

SET NOCOUNT ON
DECLARE @userId INT

IF object_id('tempdb.dbo.#user') IS NOT null
     SELECT @userId = userid FROM #user

UPDATE h
SET userId = @userId
FROM HelloWorld h
  INNER JOIN INSERTED i
    ON h.id = i.id


GO

INSERT INTO HelloWorld(a,b) VALUES (1,2)

GO
SELECT * FROM HelloWorld
GO
CREATE TABLE #user(userId INT)
     INSERT INTO #user(#user.userId) VALUES(999)
INSERT INTO HelloWorld(a,b) VALUES (1,2)
GO
SELECT * FROM HelloWorld
GO
DROP TABLE HelloWorld
DROP TABLE #user

Open in new window

0
 
BrandonGalderisiCommented:
So in the above SQL, instead of putting 999 in the #user table (which would be created and populated in your procedure code) you would put the userID value that you looked up.  It can be done once per procedure call, then any/all triggers could reference the data.
0
 
Emanuele_CiriachiAuthor Commented:
Hello BrandonGalderisi,

thank your your useful input. It is a valid solution, and I will accept it for the sake of future readers.
However I decided that I will go on a differente path: I will work with the username, IP and session id inside the stored procedures that performs UPDATE and INSERT - the triggers will not touch those fields. I will create a function that populates local variables by looking up the session table, and eventually returns them, to be used into every stored procedure of my framework.

It suddenly dawned on me that I didn't need to do all of this inside a trigger.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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