I am trying to modify a trigger to not fire is a global variable is set to 1. In SQL how do I look up a global variable value?
DECLARE @SomeVariableValue ListYesNoType = 0
EXEC dbo.GetVariableSp@VariableName = '@SomeVariableName';, @DefaultValue = 0, @DeleteVariable = 1 , @VariableValue = @SomeVariableValue OUTPUT, @Infobar = NULL
Thank you so much!
Has anyone ever seen this error in an event handler : Assignment to a Session Variable is not possible after a suspended EventHandler is reanimated!
I am trying to set the global variable with a Set Values Action and SETGLOBVALUES() but I receive the error that it can't be set. My Event Handler is not suspended.
Not sure about your error, but this is how we would assign a variable in SQL
EXEC dbo.DefineVariableSp 'SomeVariableName', @SomeVariableValue, @Infobar OUTPUT
just noticed a typo in my prior snippet:
@VariableName = '@SomeVariableName';
should be:
@VariableName = 'SomeVariableName'
Followed up with:
EXEC dbo.UndefineVariableSp 'SomeVariableName', @Infobar OUTPUT
After the trigger would have fired.
Thank you. It does seem that the trigger is working to pull in the variable, however the part I am having trouble with is I am trying to set the value of that variable in an event handler not in the Trigger. I just want the Trigger to look up that value so I can use it as a condition of some of the checks the trigger does.
If my Variable in Infor is set to a Value of 1 - do I understand this stored procedure correctly that @SomeVariableValue would be set to 1 or because the default value is set to 0 would it set it to zero?
I believe the dbo.GetVariableSp checks for the variable. If it finds it, it returns its value, otherwise is returns the default value you provided.
Here is the code from the sproc:
DECLARE @Severity INT
SET @Severity = 0
IF dbo.VariableIsDefined(@VariableName) = 1BEGIN SET @VariableValue = dbo.DefinedValue(@VariableName) IF @DeleteVariable <> 0 EXEC @Severity = dbo.UndefineVariableSp @VariableName, @Infobar OUTPUTENDELSE SET @VariableValue = @DefaultValue
RETURN @Severity
okay thank you, for some reason it is not pulling the variable value from Infor so I must have some other issue. I'm still waiting for Infor to get back to me on the error I receive when I try to set a global variable value through an event handler.
Hi everyone, I am still not having any luck with this task so I am reaching out again to see if anyone has an idea of what I could be doing wrong. In CSI (Syteline 10 on-prem) I have created a global variable by going into edit mode and creating it and setting the value to 1. In the SQL Trigger for the Preqitems approval limits check I have declared the variable and run the GetVariableSp as suggested below. The Trigger never recognizes the value as 1. So I have then tried through an event handler to Set it and Define it both with no luck.
Here is my Action Type Set Values:
SETGLOBVALUES(_OSS_EmailApprovalVar = "1")
This gets an error from the event saying: "Assignment to a Session Variable is not possible after a suspended EventHandler is reanimated!" I have no suspended events in CSI and no other event is set to run against this IDO.
I have also tried to Define the Variable in the Event Handler using an Action Type Call Database Method:
METHOD("DefineVariableSp")PARMS("_OSS_EmailApprovalVar", "1", "")
This does not receive an error by the event but the trigger does not recognize the value of 1 and therefore will not save the status change of the record.
I am trying to bypass the limit check of the user for Purchase Order Requisitions and allow approval from Managers through email. If anyone has any insight in how to work around this without completely disabling the trigger I would greatly appreciate any help! We still need the trigger to work when someone opens the form.
Thank you in advance!
have you tried just using the variable and not assigning it? since it is defined in Infor as global and has a value to use that defined variable and its value don't define it and then it may look for the global value, pull it in and use it. it is worth a try, not sure it that is the solution though.
In CSI I set the value of the variable to 1. Then I did not generate the event, just changed the status on the form to trigger the SQL trigger to fire and it did not recognize the value of 1. That is why i thought maybe i needed the event to define the variable if it is not recognizing it otherwise. I did test changing the value of the variable to 1 in SQL to verify it will skip the limit check if the value is 1. I just cannot get it to get the value from CSI or the event handler in SQL.
Here is what I have added to the trigger in SQL:
declare @OSSApproveVar ListYesNoType exec dbo.GetVariableSp '_OSS_EmailApprovalVar', 0, 1, @OSSApproveVar OUTPUT, @Infobar OUTPUT
IF @OSSApproveVar = 0 BEGIN
Not your issue here, but your IF check should be for a string value not integer: (@OSSApproveVarshould be of type VeryLongListType (NVARCHAR(MAX)) not ListYesNoType (TINYINT).
IF @OSSApproveVar = '0' <-- single quotes
BEGIN
END
I say that because the sproc dbo.GetVariableSp has it defined that way.
CREATE PROCEDURE [dbo].[GetVariableSp](@VariableName SYSNAME, @DefaultValue VeryLongListType, @DeleteVariable ListYesNoType, @VariableValue VeryLongListType OUTPUT, @Infobar InfobarType OUTPUT )
I believe SQL will just cast it appropriately regardless, but thought I would point that out. Types can be *** when debugging stuff.
