Question
· Jul 13, 2022

How to use a variable in "{fieldName*C} "when using ObjectScript Trigger Code

#ObjectScript #SQL #Caché #InterSystems IRIS

Hi,

when I using ObjectScript Trigger Code , I want to get whether the fields has changed by using "{fieldName*C}" ,  actually, fieldName in "{fieldName*C}" is a real field name ,but in my code, fieldName is a variable as follows:

		SET stat=##class(%SYSTEM.SQL).GetColumns(tableName,.byname,.bynum,1)
		IF stat=1{
			SET i=1
			WHILE $d(bynum(i)){
				SET xColName=bynum(i)
				SET valComp={xColName*C}
				
				IF valComp=1{
					SET oldVal=1 //{xColName*O}
					SET newVal=2 //{xColName*N}
					&sql(INSERT INTO Yan.LogTableExt(Parref,ProperName,OldValue,NewValue) VALUES (:id,:xColName,:oldVal,:newVal) )		
				}
				SET i=i+1
			}	
		}

So, how do I right use xColName in {xColName*C}?
}

Discussion (5)1
Log in or sign up to continue
Pravin Barton · Jul 14, 2022

It's possible to do this by using a trigger generator. Then you can run GetColumns at compile time of the class, and use the result to write out lines of code using the {fieldName*C} syntax. Just as a warning, using generators can be tricky because it adds a layer of indirection to your code. The best way to debug is to use the "View Other" command in Studio or VS Code and look directly at the generated code.

Here is some sample code for a trigger generator:

Trigger TestTrigger [ CodeMode = objectgenerator, Event = INSERT/UPDATE, Foreach = row/object ]
{
    set tableName = %compiledclass.SQLQualifiedNameQ
    set st = ##class(%SYSTEM.SQL).GetColumns(tableName,.byname,.bynum,1)
    $$$ThrowOnError(st)
    set i = 1
    while $d(bynum(i)) {
        set xColName = bynum(i)
        do %code.WriteLine(" set ^test("""_xColName_" changed"") = {"_xColName_"*C}")
        set i = i + 1
    }
}
0 0
Michael Davidovich · Nov 8, 2022

Hi @Pravin Barton, I was looking for this exact advice however, I am having trouble debugging.  I have looked at the generated INT code and I'm a bit stumped.  

All our %Persistent objects are updated via object access in CSP pages calling class methods.  I utilzed the example you have below but I used set tableName = %compiledclass.NameGet() to get the tableName.  The properties wrote to ^test no problem, so that's not an issue.

The issue is after updating objects using the CSP pages (i.e. the front end), all the checks for {property*C} are false (0).  I see expected operations being passed into the zFunctions with the pChanged array being setup.  All our inserts and updates are accomplished by %Save().  We never %Delete().

I also included Time=After as a parameter in the trigger generator.  So according to the doc, the trigger should happen similar to then %OnAfterSave() method.  

Any thoughts on what I may have missed or why this isn't working in my setup?

0 0
Michael Davidovich · Nov 9, 2022

I did get my code to work as expected but there's still some unknowns.

I used the example as shown above but also added another block of code to capture the properties that changed into a log.  I also did a KILL on the ^test array before the lines of code were generated.

I either the KILL on the array and/or the number of saves and background calls happening in our system had something to do with my initial issues.  My most apparent issue however, was I wasn't using %code.WriteLine to produce the generated code.  

At the end of the day this will probably go into a local array and not a global so all the update operations don't compete.

0 0