Trigger

SENDEELEMENTE_UPD

Beschreibung

Trigger um den TISTATUS anzupassen.

Definition

CREATE TRIGGER SENDEELEMENTE_UPD FOR SENDEELEMENTE
ACTIVE AFTER UPDATE POSITION 0
AS 
  DECLARE VARIABLE ActTiId     INTEGER;
  DECLARE VARIABLE ActTiStatus INTEGER;
  DECLARE VARIABLE hasUpdated  INTEGER;
  DECLARE VARIABLE ActTiTyp    VARCHAR(2);
BEGIN
  IF ((OLD.SETIID <> NEW.SETIID) OR
      ((NEW.SETIID IS NULL) AND (OLD.SETIID IS NOT NULL)) OR
      ((OLD.SETIID IS NULL) AND (NEW.SETIID IS NOT NULL)) OR
      ((OLD.SESTATUS < 5) AND (NEW.SESTATUS = 5)) OR
      ((OLD.SESTATUS = 5) AND (NEW.SESTATUS != 5))) THEN 
  BEGIN
    
    FOR SELECT TIID, TISTATUS, TITYP FROM TITEL
    WHERE (TIID = OLD.SETIID) OR (TIID = NEW.SETIID)
    INTO :ActTiId, :ActTiStatus, :ActTiTyp
    DO BEGIN

      EXECUTE PROCEDURE TI_UPDATE_STATUS_WITH_RETURN ActTiId, ActTiStatus, ActTiTyp
        RETURNING_VALUES hasUpdated;

    END
  END
  
  /* Auf JP, NM, RP pruefen */
  IF ((OLD.SETYP in ('JP','NM','RP')) OR (NEW.SETYP in ('JP','NM','RP'))) THEN 
  BEGIN
    /* Beim Entplanen */
    IF ((OLD.SESTATUS < 5) AND (NEW.SESTATUS = 5)) THEN 
    BEGIN
      IF (OLD.SEZUSATZTEXT IS NOT NULL) THEN BEGIN
        EXECUTE PROCEDURE UPDATE_BLOB_STATUS(OLD.SEZUSATZTEXT);
      END
    END
    ELSE BEGIN
      /* Wenn beides NULL ist, dann soll nichts gemacht werden */
      IF ((NEW.SEZUSATZTEXT IS NOT NULL) OR 
          (OLD.SEZUSATZTEXT IS NOT NULL)) THEN
      BEGIN
        /* Auf Ungleichheit pruefen */
        IF (((NEW.SEZUSATZTEXT IS NULL) AND (OLD.SEZUSATZTEXT IS NOT NULL)) OR
            ((OLD.SEZUSATZTEXT IS NULL) AND (NEW.SEZUSATZTEXT IS NOT NULL)) OR
            (F_BLOBCOMPARE(NEW.SEZUSATZTEXT, OLD.SEZUSATZTEXT) = 0)) THEN
        BEGIN
          IF (OLD.SEZUSATZTEXT IS NOT NULL) THEN BEGIN
            EXECUTE PROCEDURE UPDATE_BLOB_STATUS(OLD.SEZUSATZTEXT);
          END
          IF (NEW.SEZUSATZTEXT IS NOT NULL) THEN BEGIN
            EXECUTE PROCEDURE UPDATE_BLOB_STATUS(NEW.SEZUSATZTEXT);
          END
        END
      END
    END
  END
END