Witajcie,
jestem dosyć poczatkujący, aczkolwiek rzucony od razu na glebokie wody. Musze zreprodukowac proces polegajacy na exporcie niektórych danych z jednej tabeli do drugiej. Muszę zatem wyciąć ciało triggera i opakowac je w procedure. Import jest z tabeli *rating *do tabeli *xtexport_table_1 *. Mam prośbe o sugestie, i podpowiedzi dotyczące składni zbudowania takiej proceury.
Sam doszedłem do tego że wszystkie warunkowe opcje triggera 'IF INSERTING', 'ELSIF UPDATING' powienienem usunąć, jednoczesnie potem w składni 'INSERT INTO', powinienem wmontować 'SELECT' zaciągającego dane z tabeli rating.
Powodzenia w łamigłówce i stokrotne dzięki z góry za support ;)
create or replace
TRIGGER TRG_RATING_EXP_DLS
BEFORE DELETE OR UPDATE OR INSERT
OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALIGNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODUCT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER_ACCOUNT_ID
ON RATING
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
nOperation XTEXPORT_TABLE_1.OPERATION%TYPE := 0;
nSchemaId XTEXPORT_TABLE_1.SCHEMA_ID%TYPE := 3600;
nParentTag XTEXPORT_TABLE_1.PARENT_TAG%TYPE := -1;
nChildTag XTEXPORT_TABLE_1.CHILD_TAG%TYPE := 3600;
nExportStatus XTEXPORT_TABLE_1.STATUS%TYPE := 0;
vcTransactionId XTEXPORT_TABLE_1.DBMSTRANSACTION_ID%TYPE := DBMS_TRANSACTION.LOCAL_TRANSACTION_ID;
--Fields to be exported are
nRatingId RATING.RATING_ID%TYPE := NULL;
nDnRatingAttributeId RATING.DN_RATING_ATTRIBUTE_ID%TYPE := NULL;
nPeriodId RATING.PERIOD_ID%TYPE := NULL;
nTeamId RATING.TEAM_ID%TYPE := NULL;
nAlignmentId RATING.ALIGNMENT_ID%TYPE := NULL;
nCustomerId RATING.CUSTOMER_ID%TYPE := NULL;
nAffiliationId RATING.AFFILIATION_ID%TYPE := NULL;
nProductId RATING.PRODUCT_ID%TYPE := NULL;
vcValuMax RATING.VALUE_MAX%TYPE := NULL;
vcValuMin RATING.VALUE_MIN%TYPE := NULL;
vcExternalId_1 RATING.EXTERNAL_ID_1%TYPE := NULL;
nUserAccountId RATING.USER_ACCOUNT_ID%TYPE :=NULL;
iTenantID NUMBER (16);
vcPeriodIdNULL varchar2(10):= NULL;
vcTeamIdNULL varchar2(10):= NULL;
vcAlignmentIdNULL varchar2(10):= NULL;
vcAffiliationIdNULL varchar2(10):= NULL;
vcProductIdNULL varchar2(10):= NULL;
vcUserAccountIdNULL varchar2(10):= NULL;
BEGIN
IF (Xtelligent.DI_fnRPLUser(USER) = TRUE OR Xtelligent.DI_fnIDLUser(USER) = TRUE) THEN
RETURN;
END IF;
IF (TENANT_PKG.TriggersEnabled ('XTDE') = 0 ) THEN
RETURN;
END IF;
IF INSERTING THEN
IF (:NEW.EXTERNAL_ID_1 IS NULL) THEN
:NEW.EXTERNAL_ID_1 :=:NEW.RATING_ID;
vcExternalId_1 := :NEW.RATING_ID;
ELSE
vcExternalId_1 := :NEW.EXTERNAL_ID_1;
END IF;
IF (:NEW.GLOBAL_RATING_ID IS NULL) THEN
SELECT SEQ_IS_GLOBAL_IDENTIFIER.NEXTVAL INTO :NEW.GLOBAL_RATING_ID FROM dual;
END IF;
nOperation := 1;
nRatingId := :NEW.RATING_ID;
nDnRatingAttributeId := :NEW.DN_RATING_ATTRIBUTE_ID;
nPeriodId := :NEW.PERIOD_ID;
nTeamId := :NEW.TEAM_ID;
nAlignmentId := :NEW.ALIGNMENT_ID;
nCustomerId := :NEW.CUSTOMER_ID;
nAffiliationId := :NEW.AFFILIATION_ID;
nProductId := :NEW.PRODUCT_ID;
vcValuMax := :NEW.VALUE_MAX;
vcValuMin := :NEW.VALUE_MIN;
iTenantID := :NEW.TENANT_ID;
nUserAccountId := :NEW.USER_ACCOUNT_ID;
ELSIF UPDATING THEN
IF (:NEW.EXTERNAL_ID_1 IS NULL) THEN
:NEW.EXTERNAL_ID_1 :=:NEW.RATING_ID;
vcExternalId_1 := :NEW.RATING_ID;
ELSE
vcExternalId_1 := :NEW.EXTERNAL_ID_1;
END IF;
nOperation := 0; -- update request 0
nRatingId := :NEW.RATING_ID;
nDnRatingAttributeId := :NEW.DN_RATING_ATTRIBUTE_ID;
nCustomerId := :NEW.CUSTOMER_ID;
iTenantID := :NEW.TENANT_ID;
IF (:NEW.PERIOD_ID is NULL and :OLD.PERIOD_ID is NOT NULL) THEN
vcPeriodIdNULL :='$$$$';
ELSE
nPeriodId := :NEW.PERIOD_ID;
END IF;
IF (:NEW.TEAM_ID is NULL and :OLD.TEAM_ID is NOT NULL) THEN
vcTeamIdNULL :='$$$$';
ELSE
nTeamId := :NEW.TEAM_ID;
END IF;
IF (:NEW.ALIGNMENT_ID is NULL and :OLD.ALIGNMENT_ID is NOT NULL) THEN
vcAlignmentIdNULL :='$$$$';
ELSE
nAlignmentId := :NEW.ALIGNMENT_ID;
END IF;
IF (:NEW.AFFILIATION_ID is NULL and :OLD.AFFILIATION_ID is NOT NULL) THEN
vcAffiliationIdNULL :='$$$$';
ELSE
nAffiliationId := :NEW.AFFILIATION_ID;
END IF;
IF (:NEW.PRODUCT_ID is NULL and :OLD.PRODUCT_ID is NOT NULL) THEN
vcProductIdNULL :='$$$$';
ELSE
nProductId := :NEW.PRODUCT_ID;
END IF;
IF (:NEW.VALUE_MAX is NULL and :OLD.VALUE_MAX is NOT NULL) THEN
vcValuMax :='$$$$';
ELSE
vcValuMax := :NEW.VALUE_MAX;
END IF;
IF (:NEW.VALUE_MIN is NULL and :OLD.VALUE_MIN is NOT NULL) THEN
vcValuMin :='$$$$';
ELSE
vcValuMin := :NEW.VALUE_MIN;
END IF;
IF (:NEW.USER_ACCOUNT_ID is NULL and :OLD.USER_ACCOUNT_ID is NOT NULL) THEN
vcUserAccountIdNULL :='$$$$';
ELSE
nUserAccountId := :NEW.USER_ACCOUNT_ID;
END IF;
ELSIF DELETING THEN
nOperation := -2;-- delete request -2
nRatingId := :OLD.RATING_ID;
nDnRatingAttributeId := :OLD.DN_RATING_ATTRIBUTE_ID;
nPeriodId := :OLD.PERIOD_ID;
nTeamId := :OLD.TEAM_ID;
nAlignmentId := :OLD.ALIGNMENT_ID;
nCustomerId := :OLD.CUSTOMER_ID;
nAffiliationId := :OLD.AFFILIATION_ID;
nProductId := :OLD.PRODUCT_ID;
vcValuMax := :OLD.VALUE_MAX;
vcValuMin := :OLD.VALUE_MIN;
iTenantID := :OLD.TENANT_ID;
nUserAccountId := :OLD.USER_ACCOUNT_ID;
IF (:OLD.EXTERNAL_ID_1 IS NULL) THEN
vcExternalId_1 := :OLD.RATING_ID;
ELSE
vcExternalId_1 := :OLD.EXTERNAL_ID_1;
END IF;
END IF;
INSERT INTO XTEXPORT_TABLE_1
(
XTEXPORT_ID,
SCHEMA_ID,
OPERATION,
PARENT_TAG,
CHILD_TAG,
STATUS,
TIME_STAMP,
DBMSTRANSACTION_ID,
KEY_1,
IDENTIFIER_1,
IDENTIFIER_2,
IDENTIFIER_3,
IDENTIFIER_4,
IDENTIFIER_5,
IDENTIFIER_6,
IDENTIFIER_7,
STRING_1,
STRING_2,
IDENTIFIER_8,
CODE_1,
CODE_2,
CODE_3,
CODE_4,
CODE_5,
CODE_6,
TENANT_ID
)
VALUES
(
XTEXPORT_ID_SEQ.NEXTVAL,
nSchemaId,
nOperation,
nParentTag,
nChildTag,
nExportStatus,
SYSDATE,
vcTransactionId,
vcExternalId_1,
nDnRatingAttributeId,
nPeriodId,
nTeamId,
nAlignmentId,
nCustomerId,
nAffiliationId,
nProductId,
vcValuMin,
vcValuMax,
nUserAccountId,
vcPeriodIdNULL,
vcTeamIdNULL,
vcAlignmentIdNULL,
vcAffiliationIdNULL,
vcProductIdNULL,
vcUserAccountIdNULL,
iTenantID
);
END TRG_RATING_EXP_DLS;