Solved

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

Posted on 2010-09-15
10
296 Views
Last Modified: 2012-05-10
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
Comment
Question by:Emanuele_Ciriachi
  • 4
  • 4
  • 2
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33688203
>> 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
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33688283
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33688341
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
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33688355
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33688374
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33688381
No thanks, I want an industry-strength solution that will work on a per-user basis and guarantee consistency - no cheesy approaches here.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33688391
Fair enough.

Good luck.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33688392
>>*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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33688397
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
 
LVL 1

Author Comment

by:Emanuele_Ciriachi
ID: 33689976
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

705 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

15 Experts available now in Live!

Get 1:1 Help Now