How to use 'Logged in UserID' in a 'Before Update Trigger (MSSQL)'

[markemoss]

Help

Is there a way to use the Mobile/Browser "Logged in UserID" and pass it to the "Before Update Trigger (MSSQL)" for use in CreatedBy/UpdatedBy fields in the Table?

If so how would you do it?

MarkMoss

[kotinkarwak]

Re: How to use 'Logged in UserID' in a 'Before Update Trigger (MSSQL)'

Welcome to AAForum, A new user answering this but hope will chime with the overall flow but other Alphaholics will confirm if niftier ways are possible.
First things first, we have to capture the logged in user. In my case, I use a tabbed UI implementation and the "form" that updates/inserts this record will have this code in the onDialogInitialize

dim lUser as c
lUser = a5ws_getcurrentuser()

Could also use Session.lUser = a5ws_getcurrentuser()

In server-side onAfterDialogValidate, we set-up the action Scripting that performs a save submitted data to table. It is within this function that we will define an additional operation to perform an update of the currently saved data to fill in the User name and any other data required.
Once the script tag has been added, you will need to go in edit it. Within this window, we can add more functionality. See bottom of the dialog,

http://screencast.com/t/NZWCeGYgN

Here I have added code for afterRecordInsert. See the helper note on what that function does. Create the xbasic for this. Please see the code I use in my case.

function afterRecordInsert as v (e as p)
	'debug(1)
	dim cn as sql::Connection
	dim sqlStmt as c
	dim flag as l
	dim js as c	
	flag = cn.open(e.connection.ConnectionString)
	if flag = .f. then
		js = "alert('Could not connect to database. Please contact support team.');"
	else
		js = "alert('Connected to database ok');"
	end if	
	sqlStmt = "update customer set CreatedBy = :vUser, CreateDate = Now() where id_Customer = :priKey" 
		
	dim args as sql::Arguments
	args.Add("vUser", Session.lUser)
	args.Add("priKey", e.primaryKeyOfInsertedRecord )
		
	flag = cn.Execute(sqlStmt,args)
	
	if flag = .f. then
		dim js as c
		js = "alert('Could not execute query. Error: " + js_escape(cn.CallResult.text) + "');"
		cn.Close()
	else
		js = "alert('Connection and SQL execution ok');"
	end if
end function

In the code, I am making use of the user identifier within the args.add. Also the key of the current record is now available in this function as per notes.
Hope this helps

[ecalan]

Re: How to use 'Logged in UserID' in a 'Before Update Trigger (MSSQL)'

Not sure this is exactly what you are looking for, but if all you are trying to do is capture the the current user to notate who updated/created the record, for created, just put =a5ws_getcurrentuser() as the default value of a textbox bound to the the CreatedBy field (I use this in all new record dialogs). For updated (which I have never used in my projects), either make the textbox a calculated field with the expression =a5ws_getcurrentuser() if that does not work(never tried it), you can always create a session variable as shown by Kotin and set the calculated expression to SESSION.USER. Note: you should use the Session.User format to set the variable and be sure to publish it to the client-side in properties.

[kotinkarwak]

Re: How to use 'Logged in UserID' in a 'Before Update Trigger (MSSQL)'

Ecalan, Does work!

[Mkpatil]

Re: How to use 'Logged in UserID' in a 'Before Update Trigger (MSSQL)'

If data is not bounded to the UX component.

Validating Data Before it Saved (I also used it for updating)
When you use this action, all data validation settings that you define for controls on the UX are enforced before the data is saved. However, you might want an additional level of validation. You can use the:
1) before Update SQL computed
2) before Insert SQL computed
server side events for this purpose.
Another common use of these events is to modify submitted data before it is committed.
For example, assume that the table you are editing has a field called 'timeLastUpdated' and you want to automatically store the current date/time in this field, you could put this code in your event handler

e.dataSubmitted.timeLastUpdate = "" + now()

These two event also allow you to set the:
e.abort flag. If this flag is set to .t., then the insert or update is not performed.

More here: http://news.alphasoftware.com/V12Pre...seNotesV12.htm

Regards!