--
-- Triggers creation SQL for CMS dashboard
--
-- 2006-03-29
-- Juha, Tao-Sheng

CREATE or replace TRIGGER StatusEnterTimeStampTrigger0
   BEFORE UPDATE of "SubmittedTimeStamp" on JOB
   FOR EACH ROW
   BEGIN
        if ( :old."DboardStatusId" = 'U' ) THEN
           :new."DboardStatusEnterTimeStamp" := :new."SubmittedTimeStamp" ;
           :new."DboardStatusId" := 'P' ;
        END IF;
   END  StatusEnterTimeStampTrigger0;
.
run;


CREATE or replace TRIGGER StatusEnterTimeStampTrigger1
   BEFORE UPDATE of "StartedRunningTimeStamp" on JOB
   FOR EACH ROW
   BEGIN
        if (:old."DboardStatusId" = 'P' or :old."DboardStatusId" = 'U' ) THEN
           :new."DboardStatusEnterTimeStamp" := :new."StartedRunningTimeStamp" ;
           :new."DboardStatusId" := 'R' ;
        END IF;
   END StatusEnterTimeStampTrigger1;
.
run;


CREATE or replace TRIGGER StatusEnterTimeStampTrigger2
   BEFORE UPDATE of "JobExecExitTimeStamp" on JOB
   FOR EACH ROW
   BEGIN
        if (:old."DboardStatusId" != 'T' ) THEN
           :new."DboardStatusEnterTimeStamp" := :new."JobExecExitTimeStamp" ;
           :new."FinishedTimeStamp" := :new."JobExecExitTimeStamp" ;
           :new."DboardStatusId" := 'T' ;
        END IF;
   END StatusEnterTimeStampTrigger2;
.
run;


CREATE or replace TRIGGER DboardGridEndIdTrigger0
   BEFORE UPDATE of "DboardGridEndId" on JOB
   FOR EACH ROW
   BEGIN
        if ( :old."DboardGridEndId" = 'U' or :old."DboardGridEndId" = 'A' ) THEN
           :new."GridEndStatusId" := :new."GridStatusId" ;
           :new."GridEndStatusReasonId" := :new."GridStatusReasonId" ;
           :new."GridEndStatusTimeStamp" := :new."GridStatusTimeStamp" ;
           :new."DboardStatusId" := 'T' ;
           :new."DboardStatusEnterTimeStamp" := :new."GridEndStatusTimeStamp" ;
        END IF;
   END DboardGridEndIdTrigger0 ;
.
run;


CREATE or replace TRIGGER GridStatusTrigger0
   BEFORE UPDATE of "GridStatusId" on JOB
   FOR EACH ROW
   BEGIN
       IF getGridStatusLevelFromDB(:new."GridStatusId") < getGridStatusLevelFromDB(:old."GridStatusId") THEN
          :new."GridStatusId" := :old."GridStatusId" ;
          :new."GridStatusReasonId" := :old."GridStatusReasonId" ;
          :new."GridStatusTimeStamp" := :old."GridStatusTimeStamp" ;
          :new."GridStatusSourceId" := :old."GridStatusSourceId" ;
       END IF ;
   END GridStatusTrigger0 ;
.
run;


CREATE or replace TRIGGER JobTaskIdTrigger
   BEFORE UPDATE of "TaskId" on JOB
   FOR EACH ROW
   BEGIN
        if (:old."TaskJobId" != 0  ) THEN
           :new."TaskId" := :old."TaskId" ;
        END IF;
   END JobTaskIdTrigger ;
.
run;



CREATE or replace TRIGGER InfoSourceTrigger0
   BEFORE UPDATE of "GridStatusId","GridStatusReasonId","GridStatusTimeStamp", "GridStatusSourceId" on JOB
   FOR EACH ROW
   BEGIN
        if ( :new."GridStatusSourceId" < :old."GridStatusSourceId" ) THEN
            :new."GridStatusId" := :old."GridStatusId" ;
            :new."GridStatusReasonId" := :old."GridStatusReasonId" ;
            :new."GridStatusTimeStamp" := :old."GridStatusTimeStamp" ;
            :new."GridStatusSourceId" := :old."GridStatusSourceId";
        END IF;
   END  InfoSourceTrigger0 ;
.
run;

CREATE or replace TRIGGER NextJobIdTrigger0
   BEFORE INSERT on JOB
   FOR EACH ROW
   BEGIN
        if (:new."TaskJobId" != 0  ) THEN
        -- select all job records that have the same TaskJobId
            UPDATE JOB set "NextJobId" = :new."JobId" where JOB."TaskJobId" = :new."TaskJobId" and JOB."NextJobId" is null and JOB."JobId" != :new."JobId" ;
        END IF;
   END NextJobIdTrigger0 ;
.
run;

--- THIS IS NOT WORKING !!!!!
CREATE or replace TRIGGER NextJobIdTrigger1
   BEFORE UPDATE of "TaskJobId" on JOB
   FOR EACH ROW
   BEGIN
        if (:new."TaskJobId" != 0  ) THEN
        -- select all job records that have the same TaskJobId
            UPDATE JOB set "NextJobId" = :new."JobId" where JOB."TaskJobId" = :new."TaskJobId" and JOB."NextJobId" is null and JOB."JobId" != :new."JobId" ;
        END IF;
   END NextJobIdTrigger1 ;
.
run;