Add an attribute value as parameter of a stored procedure
Cédric BLANC
started a topic
about 2 years ago
In a stepper, we can add a stored procedure with parameters. When we edit parameter, we cannot select an attribute of entity. Is it possible to add attribute's value as parameter of a stored procedure? We wish to add new records in SD table based on data entered in a form ('auto' is a synonym of 'car' in French --> 'car' is a synonym of 'auto' : if I enter 'auto', i would like to auto-create record for 'car').
Best Answer
C
Cédric BLANC
said
about 2 years ago
The question is really around the different levels of stepper triggers. The trigger you are editing in your screenshot is at the 'stepper' level and not associated with a particular 'step' or more precisely a particular record, this is why in the SemQL editor you do not have access to record level attributes. The expectation for triggers at this level is that they would not pertain to a record, but to the overall update process. I use triggers here to timestamp all records edited in this stepper using a stored procedure, for example.
If you were to add a 'step trigger' to the 'form step' you'd see you have the option to execute either a stored procedure or an enricher, and you'd have the option of providing attribute values to your stored procedure as this step is associated with a particular record. To do this, do not add a trigger to the stepper as you are doing in the screen shot, instead, select one of your collection steps (Customer, for example) and select 'step triggers' from the properties menu at the bottom of the screen.
As you configure the step trigger, you can decide if it runs as the step starts, pre-validation, post-validation, if the user selects back, etc.. The standard practice would be to select Step - Exit (Post validation - Each) which will call your stored procedure after everything is wrapped up in the step. But you may wish to call it sooner depending on your requirements.
As a side note, one practice I find particularly useful is to trigger a stored procedure from the collection step, as you have started to do here, and pass in the load ID. Using this, your stored procedure can update all records in the SD or SA tables that will be affected by this load.
To conclude, given your example, you could write a stored procedure which you would call at the collection step, this procedure would take in the ID of the record being edited and the loadid and updated the sdcar entity with the appropriate synonym. Alternatively, you could create a collection step that only passes the loadid and then update all sd_car records with that load_id based on your synonym logic. Either will work, the latter is generally considered more elegant.
In your case, if you do your update at the collection step, your synonym logic would be imbedded in a SemQL expression, the latter, your logic would have to exist in the procedure, which is maybe not ideal.
Perhaps a third option is to create an enricher, which applies your logic based on a SemQL expression, and to trigger it at the collection step. If that is possible, it's the best solution as it does not require an external stored procedure.
1 Comment
Cédric BLANC
said
about 2 years ago
Answer
The question is really around the different levels of stepper triggers. The trigger you are editing in your screenshot is at the 'stepper' level and not associated with a particular 'step' or more precisely a particular record, this is why in the SemQL editor you do not have access to record level attributes. The expectation for triggers at this level is that they would not pertain to a record, but to the overall update process. I use triggers here to timestamp all records edited in this stepper using a stored procedure, for example.
If you were to add a 'step trigger' to the 'form step' you'd see you have the option to execute either a stored procedure or an enricher, and you'd have the option of providing attribute values to your stored procedure as this step is associated with a particular record. To do this, do not add a trigger to the stepper as you are doing in the screen shot, instead, select one of your collection steps (Customer, for example) and select 'step triggers' from the properties menu at the bottom of the screen.
As you configure the step trigger, you can decide if it runs as the step starts, pre-validation, post-validation, if the user selects back, etc.. The standard practice would be to select Step - Exit (Post validation - Each) which will call your stored procedure after everything is wrapped up in the step. But you may wish to call it sooner depending on your requirements.
As a side note, one practice I find particularly useful is to trigger a stored procedure from the collection step, as you have started to do here, and pass in the load ID. Using this, your stored procedure can update all records in the SD or SA tables that will be affected by this load.
To conclude, given your example, you could write a stored procedure which you would call at the collection step, this procedure would take in the ID of the record being edited and the loadid and updated the sdcar entity with the appropriate synonym. Alternatively, you could create a collection step that only passes the loadid and then update all sd_car records with that load_id based on your synonym logic. Either will work, the latter is generally considered more elegant.
In your case, if you do your update at the collection step, your synonym logic would be imbedded in a SemQL expression, the latter, your logic would have to exist in the procedure, which is maybe not ideal.
Perhaps a third option is to create an enricher, which applies your logic based on a SemQL expression, and to trigger it at the collection step. If that is possible, it's the best solution as it does not require an external stored procedure.
Cédric BLANC
In a stepper, we can add a stored procedure with parameters. When we edit parameter, we cannot select an attribute of entity. Is it possible to add attribute's value as parameter of a stored procedure? We wish to add new records in SD table based on data entered in a form ('auto' is a synonym of 'car' in French --> 'car' is a synonym of 'auto' : if I enter 'auto', i would like to auto-create record for 'car').
The question is really around the different levels of stepper triggers. The trigger you are editing in your screenshot is at the 'stepper' level and not associated with a particular 'step' or more precisely a particular record, this is why in the SemQL editor you do not have access to record level attributes. The expectation for triggers at this level is that they would not pertain to a record, but to the overall update process. I use triggers here to timestamp all records edited in this stepper using a stored procedure, for example.
If you were to add a 'step trigger' to the 'form step' you'd see you have the option to execute either a stored procedure or an enricher, and you'd have the option of providing attribute values to your stored procedure as this step is associated with a particular record. To do this, do not add a trigger to the stepper as you are doing in the screen shot, instead, select one of your collection steps (Customer, for example) and select 'step triggers' from the properties menu at the bottom of the screen.
As you configure the step trigger, you can decide if it runs as the step starts, pre-validation, post-validation, if the user selects back, etc.. The standard practice would be to select Step - Exit (Post validation - Each) which will call your stored procedure after everything is wrapped up in the step. But you may wish to call it sooner depending on your requirements.
As a side note, one practice I find particularly useful is to trigger a stored procedure from the collection step, as you have started to do here, and pass in the load ID. Using this, your stored procedure can update all records in the SD or SA tables that will be affected by this load.
To conclude, given your example, you could write a stored procedure which you would call at the collection step, this procedure would take in the ID of the record being edited and the loadid and updated the sdcar entity with the appropriate synonym. Alternatively, you could create a collection step that only passes the loadid and then update all sd_car records with that load_id based on your synonym logic. Either will work, the latter is generally considered more elegant.
In your case, if you do your update at the collection step, your synonym logic would be imbedded in a SemQL expression, the latter, your logic would have to exist in the procedure, which is maybe not ideal.
Perhaps a third option is to create an enricher, which applies your logic based on a SemQL expression, and to trigger it at the collection step. If that is possible, it's the best solution as it does not require an external stored procedure.
Cédric BLANC
The question is really around the different levels of stepper triggers. The trigger you are editing in your screenshot is at the 'stepper' level and not associated with a particular 'step' or more precisely a particular record, this is why in the SemQL editor you do not have access to record level attributes. The expectation for triggers at this level is that they would not pertain to a record, but to the overall update process. I use triggers here to timestamp all records edited in this stepper using a stored procedure, for example.
If you were to add a 'step trigger' to the 'form step' you'd see you have the option to execute either a stored procedure or an enricher, and you'd have the option of providing attribute values to your stored procedure as this step is associated with a particular record. To do this, do not add a trigger to the stepper as you are doing in the screen shot, instead, select one of your collection steps (Customer, for example) and select 'step triggers' from the properties menu at the bottom of the screen.
As you configure the step trigger, you can decide if it runs as the step starts, pre-validation, post-validation, if the user selects back, etc.. The standard practice would be to select Step - Exit (Post validation - Each) which will call your stored procedure after everything is wrapped up in the step. But you may wish to call it sooner depending on your requirements.
As a side note, one practice I find particularly useful is to trigger a stored procedure from the collection step, as you have started to do here, and pass in the load ID. Using this, your stored procedure can update all records in the SD or SA tables that will be affected by this load.
To conclude, given your example, you could write a stored procedure which you would call at the collection step, this procedure would take in the ID of the record being edited and the loadid and updated the sdcar entity with the appropriate synonym. Alternatively, you could create a collection step that only passes the loadid and then update all sd_car records with that load_id based on your synonym logic. Either will work, the latter is generally considered more elegant.
In your case, if you do your update at the collection step, your synonym logic would be imbedded in a SemQL expression, the latter, your logic would have to exist in the procedure, which is maybe not ideal.
Perhaps a third option is to create an enricher, which applies your logic based on a SemQL expression, and to trigger it at the collection step. If that is possible, it's the best solution as it does not require an external stored procedure.
-
Extend a model with new entities or attributes
-
Data types in xDM
-
Effective date on entities
-
Search using wild cards
-
Export a model from production and import on a development environment
-
"Allow Delete" vs "Allow Removal" privileges
-
LOV label in Named Query
-
Select location on a map and save coordinates
-
Is there a way to set up a master-detail relationship on browse mode?
-
Choose Either a Stepper or A Workflow Based on The User Privileges
See all 270 topics