Project and Portfolio Management Practitioners Forum
cancel

Trigger to synchronize changes from request to Project and backwars

Highlighted
mvillalobos
Super Contributor.

Trigger to synchronize changes from request to Project and backwars

 

Hello

I am trying to synchronize the changes from a Request to the project asociated

 


To do it, I thougt the best way is to create a trigger that changes the fields when one of them change.

I am trying to modificate the email field in a request when I modificate it in a project.


I am using:

"CREATE OR REPLACE TRIGGER ppmuser.correo

AFTER UPDATE OF PARAMETER26 ON ppmuser.KCRT_REQ_HEADER_DETAILS

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

UPDATE PPMUSER.KCRT_REQ_HEADER_DETAILS

SET PARAMETER26 = :NEW.PARAMETER26

WHERE PARAMETER4 = :NEW.PARAMETER4 and REQUEST_ID <> NEW.REQUEST_ID;

END"


 

But it fails

 

Have you done something similar?

 

Thank you very much

6 REPLIES
Celil
Acclaimed Contributor.

Re: Trigger to synchronize changes from request to Project and backwars

Hi,

You said in code when new requestID not same with old one do .... But it is not possible in KCRT_REQ_HEADER_DETAILS table. This field the key field.

Could you please write the error down here?

Celil

Celil

IT Governance Professional
& PPM Solution Architect
mvillalobos
Super Contributor.

Re: Trigger to synchronize changes from request to Project and backwars

Hello!

 

First, thank you for answering.

 

In this table. appears Requests and Projects (i create all the projects from a request with Create_project execution step, so the project id is always bigger than the request.

 

The PARAMETER4 is a project code generated by a function (with a sequence, date and other things) and it is always the same in the project and in the request that created this project. 

 

The REQUEST_ID is not the same, when the request creates a project, the project id is (sequence id +1)

 

In header_details.jpg you can see the table KCRT_REQ_HEADER_DETAILS with IDs and Parameter4

 

 

 

 

 

mvillalobos
Super Contributor.

Re: Trigger to synchronize changes from request to Project and backwars

The server log:

 

JSESSIONID=BC09C2E1D98AE1E26AF6A14E2F77150E.HPPPM,USERNAME=admin server:http-0.0.0.0-8080-Processor13:com.kintana.web:2011/06/06-12:22:17.134 CEST: 

ORA-04098: el disparador 'PPMUSER.CORREO' no es válido y ha fallado al revalidar {KCRT_REQ_HEADER_DETAILS_TH-400}
Exception Correlation: GUID=99CCB75C-D884-4B0E-88A9-5F1B3DF4097DGenerated Time=2011/06/06-12:22:17.119 CESTServer Node Name: HPPPMat com.kintana.core.arch.TableHandlerInfo.extractResult(TableHandlerInfo.java:517)at com.kintana.core.arch.DataBeanTHPersister.save(DataBeanTHPersister.java:252)at com.kintana.core.arch.DataBeanTHPersister.save(DataBeanTHPersister.java:167)at com.kintana.core.arch.BizBeanCollectionDBAdapter.invokeSaver(BizBeanCollectionDBAdapter.java:328)at com.kintana.core.arch.BizBeanCollectionDBAdapter.save(BizBeanCollectionDBAdapter.java:202)at com.kintana.crt.server.RequestHeaderDetailDBAdapter.save(RequestHeaderDetailDBAdapter.java:52)at com.kintana.crt.server.RequestDBAdapterImpl.saveOnly(RequestDBAdapterImpl.java:403)at com.kintana.crt.server.RequestDBAdapterImpl.saveNoCommit(RequestDBAdapterImpl.java:365)at com.kintana.core.arch.DBAdapter.saveWrapper(DBAdapter.java:131)at com.kintana.core.arch.Home.save(Home.java:173)at com.kintana.crt.bean.UserRequest.save(UserRequest.java:310)at com.kintana.crt.bean.UserRequest.save(UserRequest.java:295)at com.kintana.crt.web.ctrl.RequestUpdateController.update(RequestUpdateController.java:722)at com.mercury.itg.servlet.ActionMonitorFilter.doFilter(ActionMonitorFilter.java:82)at com.mercury.itg.servlet.HibernateSessionFilter.doFilter(HibernateSessionFilter.java:79)at com.kintana.core.web.filter.MLUFilter.applyFilter(MLUFilter.java:110)at com.kintana.core.web.filter.BaseFilter.doFilter(BaseFilter.java:59)at com.kintana.core.web.filter.stinger.ValidationFilter.applyFilter(ValidationFilter.java:93)at com.kintana.core.web.filter.stinger.ValidationFilter.doFilter(ValidationFilter.java:66)at com.kintana.core.web.filter.MultipartRequestFilter.applyFilter(MultipartRequestFilter.java:108)at com.kintana.core.web.filter.BaseFilter.doFilter(BaseFilter.java:59)at com.kintana.core.web.filter.ControlFilter.applyFilter(ControlFilter.java:825)at com.kintana.core.web.filter.ControlFilter.doFilter(ControlFilter.java:1318)at com.mercury.itg.servlet.I18NFilter.doFilter(I18NFilter.java:40)at com.kintana.core.web.filter.Log4jFilter.applyFilter(Log4jFilter.java:49)at com.kintana.core.web.filter.BaseFilter.doFilter(BaseFilter.java:59)at com.kintana.core.web.filter.PerformanceFilter.applyFilter(PerformanceFilter.java:57)at com.kintana.core.web.filter.BaseFilter.doFilter(BaseFilter.java:59)
Filtered Out 49 Stack Trace Lines.

Utkarsh_Mishra
Acclaimed Contributor.

Re: Trigger to synchronize changes from request to Project and backwars

Your query require little modification.

 

  1. UPDATE PPMUSER.KCRT_REQ_HEADER_DETAILS

    SET PARAMETER26 = :NEW.PARAMETER26

    WHERE PARAMETER4 = :NEW.PARAMETER4 and REQUEST_ID <> NEW.REQUEST_ID;

    This will update all the Requests execpt the one i.e. NEW.REQUEST_ID

  2. Secondly in trigger you cannot update the field which is already in use/referenced (PARAM 26). This will cause the mutating error.


    The Best way to resolve your issue is to create a RULE in your request type.
Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
mvillalobos
Super Contributor.

Re: Trigger to synchronize changes from request to Project and backwars

The other way is an execution step that calls to this script:

 

The call:

ksc_local_exec sqlplus.exe SYSTEM/PASSWORD@PPM8 @C:\Modifica-campos.sql ('[REQ.VP.CODIGO_PROYECTO]') ('[REQ.VP.CORREO_ELECTRONICO]') ('[REQ.VP.TELEFONO]') ('[REQ.VP.CODIGO_CLIENTE]') ('[REQ.VP.PLAZO_VALIDEZ_PROPUESTA]') ('[REQ.VP.PLAZO_PROPUESTA]') ('[REQ.REQUEST_ID]') ('[REQ.DESCRIPTION]')


The script:
"DEFINE proyecto = &1;

DEFINE correo = &2;

DEFINE telefono = &3;

DEFINE Codigo = &4;

DEFINE validez = &5;

DEFINE elaboracion = &6;

DEFINE solicitud = &7;

UPDATE PPMUSER.KCRT_REQ_HEADER_DETAILS SET PARAMETER26 = &correo, VISIBLE_PARAMETER26 = &correo, PARAMETER25 = &telefono, VISIBLE_PARAMETER25 = &telefono, PARAMETER5 = &codigo, VISIBLE_PARAMETER5 = &codigo, PARAMETER13 = &validez, VISIBLE_PARAMETER13 = &validez, PARAMETER14 = &elaboracion, VISIBLE_PARAMETER14 = &elaboracion, LAST_UPDATE_DATE = (SELECT SYSDATE FROM DUAL), LAST_UPDATED_BY =1

WHERE PARAMETER4 = &proyecto AND REQUEST_ID < &solicitud;


UPDATE PPMUSER.KCRT_REQUEST_DETAILS SET LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = 1

WHERE REQUEST_Id = (SELECT REQUEST_ID FROM PPMUSER.KCRT_REQ_HEADER_DETAILS

WHERE PARAMETER4 = &proyecto AND REQUEST_ID < &solicitud);


UPDATE PPMUSER.KCRT_REQUESTS SET CONTACT_ID = (SELECT CONTACT_ID FROM PPMUSER.KCRT_REQUESTS WHERE REQUEST_ID = &solicitud), DESCRIPTION = (SELECT DESCRIPTION FROM PPMUSER.KCRT_REQUESTS WHERE REQUEST_ID = &solicitud), LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY = 1WHERE REQUEST_ID = (SELECT REQUEST_ID FROM PPMUSER.KCRT_REQ_HEADER_DETAILS

WHERE PARAMETER4 = &proyecto AND REQUEST_ID < &solicitud);


COMMIT;
EXIT;
EXIT;"

 

It works propertly but i think it would be cleaner with a trigger, because the workflow needs a lot of transitions with this way.

 


Utkarsh_Mishra
Acclaimed Contributor.

Re: Trigger to synchronize changes from request to Project and backwars

I believe you were asking for sync on field change.

 

Well its great :smileyhappy: if your task is achieved.

 

Also I would recommned to use a function instead of Procedure, as you are calling a procedure from a command. And the command execution is resource expensive (as the logs are generated for the command execution).

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)