1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Convert SQL trigger into procedure

Discussion in 'SQL PL/SQL' started by matulapox, Sep 12, 2011.

  1. matulapox

    matulapox Guest

    Hello guys !
    I am quite a beginner, and you would like to ask you for some help and suggestions. One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).
    I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.
    I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.

    Could you please help me out in here ??
    Many thanks in advance !!

    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 LD.PERIOD_ID is NOT NULL) THEN
    vcPeriodIdNULL :='$$$$';
    ELSE
    nPeriodId := :NEW.PERIOD_ID;
    END IF;
    IF :)NEW.TEAM_ID is NULL and LD.TEAM_ID is NOT NULL) THEN
    vcTeamIdNULL :='$$$$';
    ELSE
    nTeamId := :NEW.TEAM_ID;
    END IF;

    IF :)NEW.ALIGNMENT_ID is NULL and LD.ALIGNMENT_ID is NOT NULL) THEN
    vcAlignmentIdNULL :='$$$$';
    ELSE
    nAlignmentId := :NEW.ALIGNMENT_ID;
    END IF;

    IF :)NEW.AFFILIATION_ID is NULL and LD.AFFILIATION_ID is NOT NULL) THEN
    vcAffiliationIdNULL :='$$$$';
    ELSE
    nAffiliationId := :NEW.AFFILIATION_ID;
    END IF;

    IF :)NEW.PRODUCT_ID is NULL and LD.PRODUCT_ID is NOT NULL) THEN
    vcProductIdNULL :='$$$$';
    ELSE
    nProductId := :NEW.PRODUCT_ID;
    END IF;

    IF :)NEW.VALUE_MAX is NULL and LD.VALUE_MAX is NOT NULL) THEN
    vcValuMax :='$$$$';
    ELSE
    vcValuMax := :NEW.VALUE_MAX;
    END IF;
    IF :)NEW.VALUE_MIN is NULL and LD.VALUE_MIN is NOT NULL) THEN
    vcValuMin :='$$$$';
    ELSE
    vcValuMin := :NEW.VALUE_MIN;
    END IF;

    IF :)NEW.USER_ACCOUNT_ID is NULL and LD.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 := LD.RATING_ID;
    nDnRatingAttributeId := LD.DN_RATING_ATTRIBUTE_ID;
    nPeriodId := LD.PERIOD_ID;
    nTeamId := LD.TEAM_ID;
    nAlignmentId := LD.ALIGNMENT_ID;
    nCustomerId := LD.CUSTOMER_ID;
    nAffiliationId := LD.AFFILIATION_ID;
    nProductId := LD.PRODUCT_ID;
    vcValuMax := LD.VALUE_MAX;
    vcValuMin := LD.VALUE_MIN;
    iTenantID := LD.TENANT_ID;
    nUserAccountId := LD.USER_ACCOUNT_ID;
    IF :)OLD.EXTERNAL_ID_1 IS NULL) THEN
    vcExternalId_1 := LD.RATING_ID;
    ELSE
    vcExternalId_1 := LD.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;
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Avoiding IF condition may not solve the mess you are facing.
    Btw, what is "LD." in the following code? Do you mean ":OLD."?

    Code (SQL):
     
    IF (:NEW.VALUE_MAX IS NULL AND LD.VALUE_MAX IS NOT NULL) THEN
    vcValuMax :='$$$$';
    ELSE
    vcValuMax := :NEW.VALUE_MAX;
    END IF;
     
    This could be translated used SQL as

    Code (SQL):

    SELECT NVL2(:OLD.VALUE_MAX,NVL(:NEW.VALUE_MAX ,'$$$$',:NEW.VALUE_MAX),:NEW.VALUE_MAX)
    INTO  vcValuMax
    FROM  DUAL ;