0

I am new to DB Oracle, When I create a new request in Clarity (that is a project & portfolio management application) or when I change the status of a request, I would like to update the field status to the new value of mb_status_idea.

The following query works well in case of Update, but if I create a new request, it does not update the status. (so status is not equal to status MB). 

IF ( :old.mb_status_idea != :new.mb_status_idea) 
THEN  update inv_investments a 
set a.status = stat 
where a.id=:new.id ;
END IF;

I think the problem is that when creating a new request, since for insert trigger OLD contains NO VALUE, so the condition would be false and it doeas not update the status.

Note: The field status is in the table INV_INVETMENTS , (stat := :new.mb_status_idea) and database column for status MB is mb_status_idea

I also added this condition --> or (:old.mb_status_idea is null), but again when I create a new request, the value of "Status" and "status MB" are different (status is not updated).

I do appreciate if someone could help to overcome this problem.

All ideas are highly appreciated,

Mona

Priscilla Jobin
  • 609
  • 7
  • 19
mOna
  • 2,341
  • 9
  • 36
  • 60

1 Answers1

1

With Clarity it is recommended to not use triggers for a couple of reasons... jobs and processes may sometimes change the values of some fields at other times than when edits happen through the application. You can't control these. Triggers can't be used if you use CA hosting services. Triggers will have to be removed for upgrades because the upgrade process breaks them.

For this type of action I would recommend using the process engine. You can setup a process to run any time the field is updated. The update could be performed by a custom script or a system action. The system action is fairly straight forward to configure. If you use a custom script there are examples in the admin bookshelf documentation. You would write a SQL update statement and put it in a GEL script.

Coda
  • 387
  • 2
  • 9