Pass a value to a trigger...

Question

Post reply

Pass a value to a trigger...

Peter-218449

Mr or Mrs. 500

Points: 557
More actions
October 11, 2006 at 4:06 am

#153225

Hi All,
Here is my issue. I need to keep an audit trail of changes made to the database (insert/update/delete). I have one generic audit table which keeps track of the table that was changed, the field that was changed, the old value, the new value, the type of change, the time it took place and the user who made the change. I have created triggers on the tables that need to be audited.
The problem is the username. We use SQL authentication but the user name I want to store in the audit table is the Windows username.
What I want to do is pass the Windows username as a parameter into a stored procedure that changes a table. No problem. Then somehow pass the parameter to the trigger, or make it accessible to the trigger somehow. I have not found a way to do this yet.
Any thoughts?
Thanks in advance,
Pete

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You may use temp table. It gonna be accessible from trigger.

SELECT @username as WinUser

INTO #CurrentUser

But that's not a way.

Are you sure the table you are trying to audit will be updated only from this SP?

What if there is another process changing data in this table and having nothing to do with Windows usernames?

Does it mean your trigger will not work?

You must not reference in trigger any values not included into tables inserted, deleted, except system functions. Otherwise you gonna be in trouble.

_____________
Code for TallyGenerator

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 2

Hi Sergiy,

Thanks for the reply. Let me explain my situation a little bit more.

I need to audit every change to every table. No windows users have access to the database, they don't even have Login accounts.

Our app maps Windows users to generic SQL users who do have access to the DB. The SQL users can call stored procedures to make changes to the tables. All table changes are done through stored procedures.

I liked your idea about using temp tables. I have a few questions about it though. I think I would need to create and drop the temp table within the stored procedure and set the transaction isolation level to Serializable in order to ensure that:

1) I get the correct value

2) Do not have someone trying to create a temp table that already exists.

Is this correct?

Also, I would need to have different named temp tables in each stored procedure that changed a table in order to avoid a naming conflict. Is this correct?

Thanks again for the reply. I am interested to hear your thoughts.

Pete

Sergiy SSC Guru Points: 110208 More actions · Answer 3

You probably don't understand my point.

Tomorrow the guy sitting next to you will manually correct data in the table from EM.

What will happen to your trigger?

What user name will be recorded in your audit table?

Will it be helpful?

Next day another developer will create a job modifying this table. He would not know about your great idea to pass parameter to trigger. And his job will fail or produce unexpectable results.

If you'll think about this you'll realise some more scenarios when your effort will cause failure of your system.

Trigger is not part of your SP. It's part of functionality assigned to the table. And it must operate with only data it can get from the table.

About temp tables.

All your assumptions are wrong.

You need to open BOL and read toppic CREATE TABLE, chapter "Temporary Tables"

_____________
Code for TallyGenerator

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 4

Thanks for the reply.

Perhaps I haven't explained things as clearly as I should have.

When we ship our app, the database will be blank and the audit trail will not be active. They can choose to import data we will provide and they can also choose to activate the audit trail.

So during development, it really doesn't matter what is going into the audit trail. Customers will only be accessing data through stored procedures and it is their audit trail with which I am concerned.

I realize that the trigger is not part of my stored procedure. However it is within my stored procedure where I will execute insert/update/delete statements. If I am to use a temp table like you suggested, it must be created and the data inserted before I execute the insert/update/delete statement within the procedure so the table will exist when the trigger is executed.

I took a look at BOL and you are correct. SQL Server takes care of those issues for me.

I guess what I can gather from your comment that a trigger "must operate with only data it can get from the table" is that your suggestion of temporary tables isn't the right approach.

If that is true, what do you think my best option is given the scenario I have described?

If I cannot get a parameter/temp table value into my trigger, the only other option I can see is implementing a stored procedure that writes to my audit table. This seems like a lot of unnecessary bulky code and I would like to avoid it if possible.

Thanks for the input and I'm interested to hear your thoughts.

Pete

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

I guess I don't understand... obviously, you are finding the correct Windows user name somewhere along the line in your stored procedure because you want to "pass it" to a trigger... if you were to turn that into a function and call the function from the trigger, it would work as expected. Wait, don't do that quite yet...

Can you post the code, please, that you made to find the Windows system user? It would be an important clue to this problem and I also interested in seeing how you did it. Thanks.

Oh yeah... I'm thinking that the System_User function would probably take care of the whole thing... from BOL... see the hi-lited stuff

Syntax

SYSTEM_USER

Remarks

Use the SYSTEM_USER niladic function with DEFAULT constraints in either the CREATE TABLE or ALTER TABLE statements, or use as any standard function.

If the current user is logged in to Microsoft® SQL Server™ using Windows Authentication, SYSTEM_USER returns the Windows 2000 or Windows NT 4.0 login identification name, for example, DOMAIN\user_login_name. However, if the current user is logged in to SQL Server using SQL Server Authentication, SYSTEM_USER returns the SQL Server login identification name, for example, sa for a user logged in as sa.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 6

Hi Jeff,

Thanks for the comments. I seemed to have confused you about where I am getting the windows username. Allow me to clarify.

I get the windows username from the client as a string. I then pass that string into my stored procedure as a parameter.

The stored proc executes an insert/update/delete statement and the trigger fires. I need the parameter that contains the username passed into the stored proc to be available to the trigger.

I agree System_User would be a great solution, but the problem is the user is logging into SQL Server using SQL authentication. So the non-hilited part is what applies to me.

Any thoughts?

Thanks,

Pete

Sergiy SSC Guru Points: 110208 More actions · Answer 7

I think you must log activity related to SP FROM SP.

And that's the place where you may record the value of your supplied parameter.

If you want to log activity related to Table you must record only data existing in inserted/deleted tables + environment parameters.

Trigger has nothing to do with some parameters of spme freaky SPs.

_____________
Code for TallyGenerator

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 8

If you are only concerned with auditing the use of the stored procedure, why not write to the audit log from the stored procedure itself?

Or, add LastUpdatedBy column to the table, and update with the username. (Probably good idea to add LastUpdatedDate column also.) Then your username will be part of the inserted table.

Mark

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 9

Since u are using windows login from APP and connecting SQL through SQL Login there are few options

1. Your procedure is modified to pass username and it takes care of auditing as well. Problem is that you will have no clue about any modification done using Enterprise Manager and Query Analyser.

2.Create a Local Windows Group. Put the application users in local windows group. Grant access to the local Windows Group and you can use SYSTEM_USER to get the username.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 10

Peter,

Thanks for being patient with the explanation... It would be really nice to combine both methods so that if something other than your proc fires the trigger, you can still find out who did what... I gotta think about this one for a minute or two...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Peter-218449 Mr or Mrs. 500 Points: 557 More actions · Answer 11

Hi all,

Thanks for all the replies. It's beginning to sound as though I should use a stored procedure to update my audit table. I was really hoping to avoid this since this will be a lot of extra code that a trigger would just take care of automatically.

The suggestion of adding a lastupdated column onto the table is an interesting one. That would allow me to use triggers, but it seems strange to add a column to every table in order to fulfill the audit requirement in a particular way (via triggers).

Any other suggestions would be welcome. Please keep in mind that I cannot grant windows users access to the db and stored procedures are the only way data will be entered (nothing via QA or EM).

Thanks again.

Pete

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 12

The suggestion of adding a lastupdated column onto the table is an interesting one

Actually, I make it common practice to add LastUpdatedBy and LastUpdatedDate for virtually all tables. Even without an audit trail requirement for a system, it is a simple way to possibly help answer the inevitable questions of "who made that change" or "when is the last change done" or "is this record/account/customer still active". And if a full audit trail is wanted later, it makes it easier to implement.

Mark

lholman SSCrazy Points: 2810 More actions · Answer 13

I agree with Mark in whole, I do this also, capturing the created_date, created_by, updated_date, updated_by along with a GUID and rowstamp (just incase replication is implemented at a later date) for all non-static tables. That way you can happily use SYSTEM_USER to return the Windows username throughout stored procedures, triggers, funtions etc, however if using SQL Server Authentication the SQL Server login name is returned, not the database username, you will need to look at SESSION_USER if you want to return the username for the particular session.

Remember though that MS has been saying for years that you should be using Windows Authentication where ever possible.

ll

lholman SSCrazy Points: 2810 More actions · Answer 14

Our app maps Windows users to generic SQL users who do have access to the DB.

Could you explain the reasoning for this? Why can't your app just pass (delegate) the Windows Credentials straight to the SQL Server, then you could manaage all your SQL access through Active Directory Groups and use the SYSTEM_USER function? This current solution sounds like quite a round the houses way of managing security.

ll