Modifying the value of a variable OnSqlTrigger - TrakCare
Hi everyone,
This is the first time I'm dealing with a request of modifying a variable value in a UDF OnSQLTrigger on InterSystems TrakCare.
I can't find any documentation to address this issue, but basically, my goal is to modify the value of one of the fields in a table when it is first inserted.
This is basically what the code should do, namely, to change the content of the %d variable at a specific key.
I have done quite a bit of debugging and seen that the %d has been correctly modified after I set %d(51) = <something>, but the value saved in the database it is still the original %d(51) value.
ClassMethod ModifyOnSQLTrigger() As%Status
{
Set sc = $$$OKTry {
If%oper="INSERT" {
Set%d(51) = 1
}
} Catch ex {
Set sc=ex.AsStatus()
Do ex.Log()
}
Return sc
}Actually, I don't know if what I would like to do is technically possible. If it isn't, do you have any workaround?
Any suggestions are welcomed :)
Thanks!
Comments
If your goal is to set a value on insert, you don't need a trigger for that. You can just declare that property with an InitialExpression defined.
Hi David, unfortunately it is impossible to modify the initial expression of that value. Because of the TrakCare framework, the particular class whose property belongs is automatically generated and, I don't know if it is possible for core team, but I'm not allowed to modify it.
There are a few items to consider with the example above:
- I would expect that UDFs are being triggered through the "Features Framework" which acts as a "wrapper" of sorts for User Defined Functions. It allows us to define multiple methods for a class and event which can be enabled or disabled based on site requirements. If you're adding calls to websys.UserDefinedFunctions and the Features Framework is enabled, you will lose the manually added call next time a patch is applied.
- The features framework explicitly protects %d and %old so they cannot be overwritten or modified inside an SQL trigger as doing so can have unpredictable results - a large number of TrakCare classes use SQLStorage.
- We need to know what you're trying to achieve. There are a couple of approaches to setting default values like this.
- UDFs return %Boolean values, not %Status codes.
Hi Jolyon, thanks for the clarifications. I'm quite experienced with Feature Framework but I've never worked with OnSQLTrigger UDFs and felt confused about them, mostly due to some information I received and probably misread. Now it is more clear and sounds logic to me that variables %d and %old can't be overwritten, as well as %response and %session, I suppose.
What I'm trying to do is to force the default value of a boolean property of a questionnaire to be 1 on insert. I was trying with OnSQLTrigger because I can't modify the Region model of the questionnaire to set a Custom default value, nor use an OnAfterSave UDF because I can't know if the questionnaire has never been inserted, and not even an OnBeforeSave because I noticed that, for whatever reason, TrakCare API calls don't trigger them.
Ah. Thanks for clarifying. That makes a bit more sense now but also raises another question. Are you creating the questionnaire through an API call, or is the questionnaire being added/updated as a result of an API call to a different component? If it's the former, you should just be able to populate the property when it's created.
I can't think of any reason why OnBeforeSave wouldn't fire in any case. I can have a look on Monday when I'm in front of my work computer. I've done similar to what you're trying to do and I would usually set a flag using a PPG in OnBeforeSave if %request.Data("ID",1)="" and then check that in the OnAfterSave and update the item then. Alternatively if there's no ID (like above), you could set %request.Data(<component item name>,1) = 1 in OnBeforeSave and the %request value would be used when it populates the object values to save.
Hi Jolyon, thanks for replying me. The questionnaire is added by API call but the problem is that this call happens in a lot of different integrations and classes and in many different ways (due to a previously bad code management there is not a single primitive class being invoked but several), so they basically asked me to somehow resolve the problem at its root and set the default value through an UDF. I don't know why the OnBeforeSave isn't triggered by the API call and this surprised me honestly, maybe is an issue related to the particular TrakCare version or to the Region/Edition code but I'm not allowed to fix/modify it unfortunately.
However, so you're saying that with an OnBeforeSave is possible to set/modify the %request value? Doing for example something like:
set Q31 = %request.Get("Q31")
If ((Q31 = "") || (Q31 = 0)) {
Set%request("Q31",1)=1
}and the object will be saved with the overwritten value?
That's correct (although from your example I would use $get(%request.Data("Q31",1)) over the "Get" method). I had another look and an assumption I had made in my previous post was partially incorrect. At least in questionnaires you can safely check the IsNew property of %UDF - this is correctly set when a questionnaire is created using either an API or through the UI. This value is derived from a variable that is set at the very top of the save so it allows us to identify a new added record. Here is a fairly simple (if a little crude) example:
ClassMethod Test()
{
tstartdo..GetAPI(.objAPI)
set objAPI.QUESPAPatMasDR = 5878set objAPI.QUESPAAdmDR = 11480set objAPI.Q01 = "Y"set rc = objAPI.Save()
zwrite {"Q01":(objAPI.Q01)}
set id = objAPI.ID
kill objAPI
set obj = ##class(questionnaire.QAUXXARP).%OpenId(id)
zwrite {"Q01":(obj.Q01)}
kill obj
do..GetAPI(.objAPI)
set rc = objAPI.Open("",id)
set objAPI.Q01 = "Y"set rc = objAPI.Save()
zwrite {"Q01":(objAPI.Q01)}
kill objAPI
set obj = ##class(questionnaire.QAUXXARP).%OpenId(id)
zwrite {"Q01":(obj.Q01)}
trollback
}
ClassMethod OnAfterSave() As%Boolean
{
#dim%UDFas websys.UDFExt.TrakEvent
set sc = $$$OKwrite !,"%UDF.IsNew = "_%UDF.IsNew,!
if%UDF.IsNew {
set obj = ##class(questionnaire.QAUXXARP).%OpenId(%UDF.Id,,.sc)
if$isobject(obj) {
set obj.Q01 = "N"set sc = obj.%Save()
}
}
if$$$ISERR(sc) {
do%UDF.Message.Status(sc)
}
return1
}
do##class(Custom.AUXX.UDF.questionnaire.QAUXXARP).Test()
%UDF.IsNew = 1
{"Q01":"Y"} ; <DYNAMIC OBJECT>
{"Q01":"N"} ; <DYNAMIC OBJECT>%UDF.IsNew = 0
{"Q01":"Y"} ; <DYNAMIC OBJECT>
{"Q01":"Y"} ; <DYNAMIC OBJECT>As you can see, the value of "Q01" is only set to "N" on the first save and is not updated afterwards.
Wow Jolyon, thank you so much! I didn't know about the IsNew property and it is really interesting. I'm going to try this as soon as I can
Hi Pietro,
%d should not be modified as it might impact down the road triggers especially for TrakCare.
As i don't know what you are trying to achieve its difficult to advise directly.
For TC i usually would use a different trigger namely OnBeforeSave / OnAfterSave which will trigger based on user activity from the UI or on Object saves. Using those you could modify the value to be saved or update after it already has been persisted.
Maybe best to request assistance from support for your specific issue.
Best Regards
Timo
Hi Timo, thank you for your response and clarifications! As I wrote to Jolyon now is it clearer how %d and %old variables work in OnSQLTrigger UDFs.