Enabling Stored Procedures through Actions: (credits to Vineesh(WIPRO Infotech))
- Suppose there are two models one is parent another one is child.
For e.g. : Transfer is a parent model & Transfer_line_ item is a child and the reference connecting Transfer Line Item model with Transfer model is Transfer_Ref
- Transfer is having details like transfer order number, Location etc.
- Transfer_line_ item is having details of each employee who is getting transferred.
- There is a GetList action in the Transfer model which will list all the employees who has applied for transfer and these employees are shown in child grid of Transfer line Item model.
- Now Convert Getlist action in to a stored procedure and named it as CREATE_BULKTRANSFER_PRC (vTransfer_Id VARCHAR (255)) where vTransfer_Id is the sheetid of the current transfer model.
- Create a Boolean cell named as Delete_Flag and a delete action (named as DeleteRecord) in the Transfet_line_Item model.
- Create a Model called : TriggerProcedure
- Create a Reference cell - Transfer_ref in TriggerProcedure Model and connect it to Parent
Model (i.e. Transfer).
- Create an action called GetListCallProcedure in transfer model. It consist of following sub actions
- Create an append action using the reference cell TriggerProcedure .Transfer_ref
- Create another append action using the reference cell Transfer_Line_Item. Transfer_Ref. Click on explicit mapping àAdd new parameteràset Delete_Flag = 1
- Create a childcall action which is calling the action “DeleteRecord” in the child model Transfet_line_Item through reference cell Transfer_Ref and enter Search-Criteria as =AND(Transfer_Line_Item.Delete_Flag=1)
- This GetListCallProcedure action is shown in the UI for calling the stored procedure “CREATE_BULKTRANSFER_PRC”
- Finally create an update trigger on TriggerProcedure table.
DROP TRIGGER /*!50032 IF EXISTS */ `ins_triggerprocedure`$$
CREATE TRIGGER `ins_triggerprocedure` AFTER UPDATE ON `tbltriggerprocedure`
FOR EACH ROW BEGIN
IF NEW.Transfer_Ref IS NOT NULL