Problem recording a workstation ID

[Hagster]

Hello All,

Ok not sure if this is the correct category to post this in but ill try.

I have a large bespoked program throughout our company allowing users to manage 'works orders' and other items via web pages with cold fussion etc (normal stuff)

Just lately there is a need to trace when certain things are deleted from our database, for auditing purposes.

I set up an SQL trigger on the table in question.. see below
----------------------------------------
CREATE TRIGGER [woDelete] ON dbo.worksorders
FOR DELETE
AS
INSERT INTO Audit_Wo (Actiontaken, auser, adate, wo)
SELECT 'D', USER_NAME (), GetDate(),Del.won from Deleted Del
----------------------------------------------
so every time a worksorder is deleted I get an audit record in the Audit_wo table showing me what date, worksorder, and user deleted it.

My problem is when a user deletes a worksordeer through our off the shelf MRP program the 'USER_NAME()' is populated correctly i.e I would see DOMAIN\username in the audit table.

However if a user deletes a worksorder via my bespoked coldfussion pages the 'USER_NAME()' variable returns just 'dbo' im assuming this is because its actually being deleted by the server so therefore the user deleting it is realy the server and not the user logged into a workstation.

How can I set a variable on my cold fussion page that would then be seen by the SQL trigger, Im just not sure on how it works can I just <cfset a variable up and then put that variable in my SQL trigger or how do I pass a coldfussion variable to an SQL one. im just confussed how I would do this.

I thought id ask here although im going to go and play now see if I can resolve it as well.

Many Kind Regards Guy

[Swift]

Hagster,

I see your problem, but I think the best solution is for you to manually enter the audit trail record yourself. If it is your program, and you are the one to run the query that deletes from the first table, then you can also add a query that adds in the information you want into the audit trail table.

Swift

[cgsj_usa]

You could use the host_name() function from SQL. So, if your machines are named as the users, it would essentially be the same as the username. However, since it's a web app, they can log in from any machine, so that might not work.

Alternatively, try setting a coldfusion variable to REMOTE_USER or AUTH_USER. However, that only works if authentication is turned on and the script is protected. Also, I'm not sure that you would be able to use that variable in a trigger. You might have to break it up into two sql batches - the delete and then the insert into the audit table (do this using stored procedures...fast and efficient), like Swift was saying.

I hope that this helps.

Thanks...Chris

[Hagster · Accepted solution]

Thanks Swift and Chris,
After some messing about it was just easyer to as swift said code in the audit write myself, Thanks Chris as you stated a stored procedure done the trick.

Thanks

[cgsj_usa]

Great! Glad that I could help.

Chris