Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2009
    Posts
    9

    Question Unanswered: Oracle Trigger. Please help!!!

    Hello, I'm very new at using triggers and I cannot find the answer to my problem online or in my oracle books.

    I work for an insurance company and we have 1000+ policies that we are renewing that were previously policies for another insurance company.

    Each month we receive an excel spreadsheet with all of the policy information. After I do some data conversion, we enter the policies into our system.

    We need to track the status of each policy from start to finish.

    An example would be:
    1.Policy received with monthly load file OR
    policy received separately.

    Next step either way:

    2. a)Data conversion complete and policy is ready to enter OR
    b)Data conversion is complete and information is missing OR
    c)Data conversion is complete and policy is ineligible

    Next step for 2a):
    3. a)Policy has been entered and issued OR
    b)Policy has been entered and suspended

    Next step for 2b):
    Waiting on missing information

    Next step for 2c):
    Nothing

    Next step for 3a):
    Policy print has been pulled

    then policy print has beem mailed, etc... etc...

    What I want to do is have the data stored in an oracle database table (already done this) and for everyone to access the data via MS Access.

    The people entering the policies would work with the policies that are okay to enter and then wither issue or suspend the policies.

    For each issue or suspend, each time the policy goes from missing info to ok to enter, each time the policy goes from print pulled to print mailed, etc.. etc... I want the users to select the status from a dropdown in access to update the row.

    Where the trigger comes in:
    Each time the status changes, I want the policy ID, status and date/timestamp to be inserted as a new row into a transactions table. (I'd also like for the status date field in the monthly load table to be updated with the same date/timestamp each time the status changes)

    This would enable me to have the current status in the main table at all times and also allow me to report the lifecycle of each policy based on the date/timestamp in the transaction table. I can acheive something similar by simply having multiple status fields in the monthly load table and each new status can be entered in the next field, however this does not give me one column containing the current status at all times.

    If anyone can help me I would be VERY happy as I'm supposed to have this set up one way or another by Monday morning and I'm stuck on this part preventing me from doing the rest of the reporting and entry design.

    Thank you!!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post Operating System (OS) name & version for DB server system.
    Post results of SELECT * from v$version.
    Post DDL for tables involved

    When all else fails, Read The Fine Manual
    Coding Triggers

    http://asktom.oracle.com
    URL above contains many fine coding examples
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Posts
    9
    Microsoft Windows XP Professional Version 2002 SP3
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    "CORE 10.2.0.1.0 Production"
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production



    CREATE TABLE "LG"."OCTOBER"
    ( "ROW_1" NUMBER NOT NULL ENABLE,
    "STATUS" VARCHAR2(250 BYTE),
    "EVEN_ODD" VARCHAR2(8 BYTE),
    "NOTES" VARCHAR2(250 BYTE),
    "UIC_POLICY_NUM" VARCHAR2(43 BYTE),
    "PREMIUM_WITHOUT_POLICY_FEE" NUMBER,
    "UIC_PREMIUM__FEE_INCLUDED" NUMBER,
    "ROLLOVER_EFFECTIVE" VARCHAR2(18 BYTE),
    "EXPIRATION" VARCHAR2(10 BYTE),
    "UIC_AGT_NUM" VARCHAR2(11 BYTE),
    "UIC_SUBAGENT_NUM" VARCHAR2(16 BYTE),
    "NAMED_INSURED" VARCHAR2(255 BYTE),
    "SECOND_NAMED_INSURED" VARCHAR2(255 BYTE),
    "MAILING_STREET_ADDRESS" VARCHAR2(255 BYTE),
    "MAILING_STATE" VARCHAR2(13 BYTE),
    "MAILING_COUNTY" VARCHAR2(255 BYTE),
    "MAILING_CITY" VARCHAR2(255 BYTE),
    "MAILING_ZIP" VARCHAR2(11 BYTE),
    "MAILING_ZIP_PLUS_4" VARCHAR2(18 BYTE),
    "PROGRAM_CODE" VARCHAR2(12 BYTE),
    "PAYOR" VARCHAR2(9 BYTE),
    "PAY_PLAN" VARCHAR2(8 BYTE),
    "TERRITORY_FOR_UIC" VARCHAR2(17 BYTE),
    "CONSTRUCTION" VARCHAR2(255 BYTE),
    "PPC" VARCHAR2(3 BYTE),
    "DED_TYPE" VARCHAR2(9 BYTE),
    "YOC" VARCHAR2(4 BYTE),
    "NUM_FAMILIES" VARCHAR2(12 BYTE),
    "FIRE_DIST_CODE" VARCHAR2(14 BYTE),
    "FIRE_DIST" VARCHAR2(255 BYTE),
    "RCC" VARCHAR2(255 BYTE),
    "SQUARE_FEET" VARCHAR2(11 BYTE),
    "UIC_DWELLING_COVERAGE" VARCHAR2(21 BYTE),
    "UIC_OTHER_STRUCTURES" VARCHAR2(20 BYTE),
    "UIC_PCNT_PERS_PROPERTY" VARCHAR2(19 BYTE),
    "UIC_PERS_PROPERTY" VARCHAR2(17 BYTE),
    "PERS_LIAB" VARCHAR2(9 BYTE),
    "MED_PAYMENT" VARCHAR2(11 BYTE),
    "ADDTL_PERILS_COV" VARCHAR2(16 BYTE),
    "DOG_LIAB_ENDT" VARCHAR2(13 BYTE),
    "FIREBURG" VARCHAR2(8 BYTE),
    "FIRE" VARCHAR2(39 BYTE),
    "BURGLARY" VARCHAR2(48 BYTE),
    "HO101" VARCHAR2(5 BYTE),
    "HO105" VARCHAR2(5 BYTE),
    "HO110" VARCHAR2(5 BYTE),
    "HO140" VARCHAR2(5 BYTE),
    "HO145" VARCHAR2(5 BYTE),
    "HO160" VARCHAR2(5 BYTE),
    "HO160_ITEM_1_CLASS" VARCHAR2(255 BYTE),
    "HO160_ITEM_1_DESCRIPTION" VARCHAR2(255 BYTE),
    "HO160_ITEM_1_VALUE" VARCHAR2(18 BYTE),
    "HO160_ITEM_2_CLASS" VARCHAR2(255 BYTE),
    "HO160_ITEM_2_DESCRIPTION" VARCHAR2(255 BYTE),
    "HO160_ITEM_2_VALUE" VARCHAR2(18 BYTE),
    "HO160_ITEM_3_CLASS" VARCHAR2(255 BYTE),
    "HO160_ITEM_3_DESCRIPTION" VARCHAR2(255 BYTE),
    "HO160_ITEM_3_VALUE" VARCHAR2(18 BYTE),
    "HO160_ITEM_4_CLASS" VARCHAR2(255 BYTE),
    "HO160_ITEM_4_DESCRIPTION" VARCHAR2(255 BYTE),
    "HO160_ITEM_4_VALUE" VARCHAR2(18 BYTE),
    "HO160_ITEM_5_CLASS" VARCHAR2(255 BYTE),
    "HO160_ITEM_5_DESCRIPTION" VARCHAR2(255 BYTE),
    "HO160_ITEM_5_VALUE" VARCHAR2(18 BYTE),
    "HO161" VARCHAR2(5 BYTE),
    "HO162" VARCHAR2(5 BYTE),
    "HO201" VARCHAR2(5 BYTE),
    "HO215" VARCHAR2(5 BYTE),
    "HO301" VARCHAR2(5 BYTE),
    "HO301_NAME" VARCHAR2(255 BYTE),
    "HO301_MAILING_ADDRESS_STREET" VARCHAR2(255 BYTE),
    "HO301_MAILING_ADDRESS_CITY" VARCHAR2(255 BYTE),
    "HO301_MAILING_ADDRESS_STATE" VARCHAR2(27 BYTE),
    "HO301_MAILING_ADDRESS_ZIP" VARCHAR2(25 BYTE),
    "HO301_INTEREST_TYPE" VARCHAR2(255 BYTE),
    "HO_301_APPLICABLE_SECTION" VARCHAR2(255 BYTE),
    "RCAERC" VARCHAR2(6 BYTE),
    "RCLS" VARCHAR2(4 BYTE),
    "UI0420" VARCHAR2(6 BYTE),
    "UI0455" VARCHAR2(6 BYTE),
    "UIOSDL" VARCHAR2(6 BYTE),
    "UIEBEETX" VARCHAR2(8 BYTE),
    "LOCATION_ADDRESS" VARCHAR2(255 BYTE),
    "LOCATION_CITY" VARCHAR2(255 BYTE),
    "LOCATION_STATE" VARCHAR2(14 BYTE),
    "ZIP_CODE_OF_LOCATION_ADDRESS" VARCHAR2(28 BYTE),
    "LOCATION_COUNTY" VARCHAR2(15 BYTE),
    "FIRST_MORTGAGE_NAME" VARCHAR2(255 BYTE),
    "FIRST_MORTGAGE_NAME_CONTD" VARCHAR2(255 BYTE),
    "FIRST_MTG_STREET_ADDRESS" VARCHAR2(255 BYTE),
    "FIRST_MTG_CITY" VARCHAR2(255 BYTE),
    "FIRST_MTG_STATE" VARCHAR2(19 BYTE),
    "FIRST_MTG_ZIP" VARCHAR2(17 BYTE),
    "FIRST_LOAN_NUMBER" VARCHAR2(255 BYTE),
    "FIRST_INTEREST_NUMBER" VARCHAR2(255 BYTE),
    "SECOND_MORTGAGE_NAME" VARCHAR2(255 BYTE),
    "SECOND_MORTGAGE_NAME_CONTD" VARCHAR2(255 BYTE),
    "SECOND_MTG_STREET_ADDRESS" VARCHAR2(255 BYTE),
    "SECOND_MTG_CITY" VARCHAR2(255 BYTE),
    "SECOND_MTG_STATE" VARCHAR2(19 BYTE),
    "SECOND_MTG_ZIP" VARCHAR2(255 BYTE),
    "SECOND_LOAN_NUMBER" VARCHAR2(255 BYTE),
    "SECOND_INTEREST_NUMBER" VARCHAR2(255 BYTE),
    "THIRD_MORTGAGE_NAME" VARCHAR2(255 BYTE),
    "THIRD_MORTGAGE_NAME_CONTD" VARCHAR2(255 BYTE),
    "THIRD_MTG_STREET_ADDRESS" VARCHAR2(255 BYTE),
    "THIRD_MTG_CITY" VARCHAR2(255 BYTE),
    "THIRD_MTG_STATE" VARCHAR2(19 BYTE),
    "THIRD_MTG_ZIP" VARCHAR2(17 BYTE),
    "THIRD_LOAN_NUMBER" VARCHAR2(255 BYTE),
    "THIRD_INTEREST_NUMBER" VARCHAR2(255 BYTE),
    "REMOVE_INSPECTION_FEE" VARCHAR2(255 BYTE),
    "LG_INFORCE_PREMIUM" VARCHAR2(18 BYTE),
    "LG_POLICY_NUM" VARCHAR2(13 BYTE),
    "MAPPED_LG_AGENT_AGENCY_NUM" VARCHAR2(26 BYTE),
    "LG_AGENT_AGENCY_NUM" VARCHAR2(19 BYTE),
    "LG_AGENT_AGENCY_NAME" VARCHAR2(31 BYTE),
    "DED_1_WIND" VARCHAR2(12 BYTE),
    "DED_2_AOP" VARCHAR2(11 BYTE),
    "LG_EFFECTIVE" VARCHAR2(12 BYTE),
    "LG_EXPIRATION" VARCHAR2(13 BYTE),
    "FIRE_BURLGARY_CRED" VARCHAR2(255 BYTE),
    "TYPE_OF_CREDIT" VARCHAR2(255 BYTE),
    "LG_DWELLING_COVERAGE" VARCHAR2(20 BYTE),
    "LG_OTHER_STRUCTURES" VARCHAR2(19 BYTE),
    "LG_PERS_PROPERTY" VARCHAR2(16 BYTE),
    "LG_HO301_INTEREST_TYPE" VARCHAR2(255 BYTE),
    "LG_MODIFIED_PERS_PROPERTY" VARCHAR2(25 BYTE),
    "LG_PCNT_PERS_PROPERTY" VARCHAR2(18 BYTE),
    "UIC_OTHER_STRUCTURES_PCNT" VARCHAR2(22 BYTE),
    "UIC_DWELL_CVG_X_FACTOR" VARCHAR2(30 BYTE),
    "FACTOR" VARCHAR2(6 BYTE),
    "ROW_2" VARCHAR2(5 BYTE),
    "TRANS_CODE" VARCHAR2(20 BYTE),
    "TRANS_DATE" TIMESTAMP (6)

    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSAUX" ;


    CREATE INDEX "LG"."INDEX1" ON "LG"."OCTOBER" ("ROW_1")
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSAUX" ;



    CREATE TABLE "LG"."TRANS"
    ( "ROW_NUM" NUMBER,
    "TRANS_CODE" VARCHAR2(20 BYTE),
    "TRANS_DATE" TIMESTAMP (6)
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSAUX" ;


    CREATE INDEX "LG"."INDEX2" ON "LG"."TRANS" ("ROW_NUM", "TRANS_CODE", "TRANS_DATE")
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "SYSAUX" ;

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suggest that ROW_1 be made the Primary Key to ensure no duplicates.

    It appears you need a simple BEFORE ROW INSERT trigger.

    So post it once you have it working.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Aug 2009
    Posts
    9
    Can you give me an example for the actual trigger? I've only done one trigger and it was an after update trigger that adds the timestamp to the same row of a table. For this one, I would not be adding new rows to the OCTOBER table, I would only be updating the status. I've tried writing it several different ways and I keep getting it to "compile with warning" and then when I change something in the October table I cannot save the table because of the trigger.

  6. #6
    Join Date
    Aug 2009
    Posts
    9
    (I want an after update for each row trigger that will add in the timestamp into TRANS_DATE on the LG.OCTOBER table, then select the ROW_1, TRANS_CODE and TRANS_DATE from LG.OCTOBER and insert them as a new row in LG.TRANS into the fields ROW_NUM, TRANS_CODE and TRANS_NUM.)

  7. #7
    Join Date
    Aug 2009
    Posts
    9
    P.S. I can make ROW_1 the primary key, but since I'm in control of anything that goes into the table, I'm already ensuring that there are no duplicates in that field. I would usually make it the primary key though. Good suggestion.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I would have chose BEFORE UPDATE, but to each their own choice.

    Any trigger is precluded from issuing any SQL against the table for which it exists.

    Let me google that for you

    If/when you get compile errors in sqlplus, do the following:

    SQL> SHOW ERROR

    Read & heed #1 STICKY post at top of the forum on how to use VB <code tags>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Aug 2009
    Posts
    9
    UPDATE "LG"."OCTOBER" SET EVEN_ODD = 'v' WHERE ROWID = 'AAANoMAADAAAenUAAC' AND ORA_ROWSCN = '13024999'

    One error saving changes to table "LG"."OCTOBER":
    Row 3: ORA-04098: trigger 'SYS.TRIGGER1' is invalid and failed re-validation
    ORA-06512: at "LG.Z_TRANS", line 2
    ORA-04088: error during execution of trigger 'LG.Z_TRANS'


    CREATE OR REPLACE TRIGGER "LG"."Z_TRANS"
    after update ON lg.october
    REFERENCING NEW AS new
    FOR EACH ROW
    BEGIN
    INSERT INTO lg.trans (row_num, trans_code)
    VALUES(:new.row_1, :new.trans_code);
    END;

    /
    ALTER TRIGGER "LG"."Z_TRANS" ENABLE;

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com/pls/asktom/...29259877603106

    >UPDATE "LG"."OCTOBER" SET EVEN_ODD = 'v' WHERE ROWID = 'AAANoMAADAAAenUAAC' AND ORA_ROWSCN = '13024999'

    I'd prefer to use WHERE ROW_1 = <value>

    >Row 3: ORA-04098: trigger 'SYS.TRIGGER1' is invalid and failed re-validation

    Application triggers should NOT be owned by SYS as above

    Do you have triggers firing triggers?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Aug 2009
    Posts
    9
    This is the first I've seen of SYS.TRIGGER1. I don't have any idea what it is.
    Last edited by ZaraliciousZ; 08-22-09 at 15:29.

  12. #12
    Join Date
    Aug 2009
    Posts
    9
    I never touched any triggers before friday and I've only made 2 of them. One was just a practice one on a practice table, the other one is lg.z_trans

  13. #13
    Join Date
    Aug 2009
    Posts
    9
    I did this "alter trigger "SYS"."TRIGGER1" disable"
    and my trigger worked!!!!

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    you probably accidentally made a trigger in sys. No one in the oracle organization would name a trigger trigger1. Drop the trigger using the following command.

    drop trigger sys.trigger1;

    As a firm rule, never ever log into SYS or SYSTEM and never build anything in the SYSTEM tablespace. Doing so could cause you major problems in the database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •