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!

How to use PL/SQL in a before insert trigger to check a value...

Discussion in 'SQL PL/SQL' started by MFriend, Sep 13, 2014.

  1. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi Folks

    I’m relatively new to PL/SQL and I’ve been trying to figure out how to do this code/check in a before insert trigger.

    Info: I have two tables, BALES_FED, and GRAZING_RECORDS (I have included their structure at the bottom of this post and a diagram pic. They are linked through the PASTURE field.

    Background: Before I let a record be saved in the BALES_FED table, I need to ensure that the Pasture the user had selected (from a select list/combo box) is allowed. Choosing a particular pasture is only allowed if there are animals currently in that particular pasture or they were there in the last day (i.e. current data or current data -1) from when the hay was fed. There are over 1500 possible pasture and we want the end users to only be able to select a pasture and save the record if there were animals in that pasture when they were fed (to make sure they have selected the right pasture, etc.)
    I need to ensure that the DATE_FED column in the BALES_FED table falls within that time. The GRAZING_RECORDS table has a DATE_IN and a DATE_OUT columns. If there is a DATE_IN, but the DATE_OUT is null the record can be saved since the animals are still in the pasture (This is shown because since there is no DATE_OUT). If there is a DATE_OUT value saved, then I need to check to see if the value in the DATE_FED column in the BALES_FED table is = or after the DATE_IN column and is < or = to the DATE_OUT to allow the record to be saved.

    There might be a better way, but my thought was to check the value of the DATE_FED column using a before insert trigger when the end user tries to save the record. If the value they have selected in the DATE_FED column does not meet the constraints listed above, then they should receive an error message and the data should not be saved.
    Here is the table structure:

    (I have attached an image file of the diagram)

    CREATE TABLE "FARMING"."BALES_FED" (
    "BALES_FED" NUMBER (5, 0) DEFAULT NULL,
    "CROP" VARCHAR2 (20 CHAR),
    "CUTTING" NUMBER (5, 0),
    "STACKYARD" VARCHAR2 (25),
    "DATE_FED" DATE,
    "PASTURE" VARCHAR2 (12),
    "TARGET_LB_PER_DAY" NUMBER (3, 1),
    "FED_ID" NUMBER (15, 0),
    "SPECIES" VARCHAR2 (30),
    "BALE_YEAR" VARCHAR2 (4)
    ) --------------------------------------------------------
    -- DDL for Table GRAZING_RECORDS
    --------------------------------------------------------
    CREATE TABLE "CATTLE"."GRAZING_RECORDS" (
    "PASTURE" VARCHAR2 (12),
    "GROUP_DESCRIPTION" VARCHAR2 (50),
    "DATE_IN" DATE,
    "DATE_OUT" DATE,
    "HEAD_COUNT" NUMBER (3, 0),
    "ANIMAL_UNIT_EQUIVALENT" NUMBER (3, 2),
    "MULTI_PASTURE_USAGE" NUMBER (3, 0),
    "COMMENTS" VARCHAR2 (200),
    "POLE_SHED" VARCHAR2 (8),
    "FEEDING_LEVEL" VARCHAR2 (25),
    "OFFSPRING_AUE" NUMBER (3, 2),
    "SPECIES" VARCHAR2 (12)
    )

    For my select list I am using the following view. This view makes it much easier for them to select a pasture from the select list since it cuts down the possible choices from about 1500 to about 60 or so. The problem with it though is the original data is written to paper first and the end user may not enter the data into the database until a few days later so I need to check to ensure that the date entered for the DATE_FED column is allowed.

    Thank you for any help…. I hope this wasn’t too confusing.

    Matthew

    View I use to limit the pasture select list:

    I have a pic at: cwebpro(DOT)com/BALES_FED_GRAZING_RECORDS.png

    SELECT
    CATTLE.GRAZING_RECORDS.PASTURE,
    CATTLE.GRAZING_RECORDS.GROUP_DESCRIPTION,
    CATTLE.GRAZING_RECORDS.DATE_IN,
    CATTLE.GRAZING_RECORDS.DATE_OUT,
    CATTLE.GRAZING_RECORDS.HEAD_COUNT,
    CATTLE.GRAZING_RECORDS.ANIMAL_UNIT_EQUIVALENT,
    CATTLE.GRAZING_RECORDS.MULTI_PASTURE_USAGE,
    CATTLE.GRAZING_RECORDS.COMMENTS,
    CATTLE.GRAZING_RECORDS.POLE_SHED,
    CATTLE.GRAZING_RECORDS.FEEDING_LEVEL,
    CATTLE.GRAZING_RECORDS.OFFSPRING_AUE,
    CATTLE.GRAZING_RECORDS.SPECIES
    FROM
    CATTLE.GRAZING_RECORDS
    WHERE
    CATTLE.GRAZING_RECORDS.DATE_OUT IS NULL OR
    CATTLE.GRAZING_RECORDS.DATE_OUT = trunc(sysdate -1) OR
    CATTLE.GRAZING_RECORDS.DATE_OUT = trunc(sysdate -2) OR
    CATTLE.GRAZING_RECORDS.DATE_OUT = trunc(sysdate -3)
    ORDER BY
    CATTLE.GRAZING_RECORDS.PASTURE ASC,
    CATTLE.GRAZING_RECORDS.DATE_IN ASC
     

    Attached Files:

  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    You didn't indicate what interface you were using for the data entry so I don't know how much control you have over it. My suggestion, assuming it is possible, would be to make your pasture list of values more dynamic. Presumably on that entry screen you have a field for the user to supply the DATE_FED value. Once that value is in place, the List of values should dynamically update to show only the pastures active for the supplied date. This would be easy if you are using Application Express, for example. Without knowing what your interface is, I can't say if it is even possible. I would handle this by creating an active_pasture function that returns Y or N when supplied with a pasture name and date. Based on what you supplied, it would look something like the following:

    Code (Text):
    FUNCTION active_pasture(p_pasture    VARCHAR2,
                            p_date       DATE)
    RETURN VARCHAR2
    AS
      v_count   NUMBER;
    BEGIN
      SELECT COUNT(*)
      INTO   v_count
      FROM   cattle.grazing_records cgr
      WHERE  cgr.pasture = p_pasture
      AND    (   cgr.date_out IS NULL
              OR cgr.date_out = TRUNC(p_date)
              OR cgr.date_out = TRUNC(p_date - 1));

      IF v_count > 0 THEN
        RETURN 'Y';
      ELSE
        RETURN 'N';
      END IF;
    END active_pasture;
    The query for your list of active pastures would look something like the following:

    Code (Text):
    SELECT
    CATTLE.GRAZING_RECORDS.PASTURE,
    CATTLE.GRAZING_RECORDS.GROUP_DESCRIPTION,
    CATTLE.GRAZING_RECORDS.DATE_IN,
    CATTLE.GRAZING_RECORDS.DATE_OUT,
    CATTLE.GRAZING_RECORDS.HEAD_COUNT,
    CATTLE.GRAZING_RECORDS.ANIMAL_UNIT_EQUIVALENT,
    CATTLE.GRAZING_RECORDS.MULTI_PASTURE_USAGE,
    CATTLE.GRAZING_RECORDS.COMMENTS,
    CATTLE.GRAZING_RECORDS.POLE_SHED,
    CATTLE.GRAZING_RECORDS.FEEDING_LEVEL,
    CATTLE.GRAZING_RECORDS.OFFSPRING_AUE,
    CATTLE.GRAZING_RECORDS.SPECIES
    FROM
    CATTLE.GRAZING_RECORDS
    WHERE
    active_pasture(CATTLE.GRAZING_RECORDS.PASTURE, [date_fed_value]) = 'Y'
    ORDER BY
    CATTLE.GRAZING_RECORDS.PASTURE ASC,
    CATTLE.GRAZING_RECORDS.DATE_IN ASC
    This method would prevent someone from submitting an invalid pasture, so you would not have to check (and return an error) if they did. It would also present the person doing data entry with the smallest possible list of pastures to choose from.
     
  3. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Thank you Matthew for your help! I really appreciate it.

    The software I'm using for the data entry screen is a PHP/Javascript program called PHPMaker.

    I want to put the function into a before insert trigger because there is no way to put it into the client side program.

    I had tried Oracle Apex back at version 2 (and just a little of the early version 3) but there were a few limitations that prevented me from being able to continue using it (for example my supervisor required us to use natural PK's which often had to be multiple - no automatically created PK's with sequences.

    Last week I began doing some experimenting with version 4.2.5 and especially the new (hopefully released soon) 5.0 Apex and really like it. For my future projects I plan on switching my development to Apex, but for this one I have spent several months on it and would hate to start from scratch with a new program (There are about 2 dozen screens in it).

    Matthew
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I don't understand what you mean by that. I have never used PHPMaker. However, cascading LOVs (or "Dynamic Selection Lists" as PHPMaker apparently calls them) are common to most web front ends. The following link is a tutorial on how to do this in PHPMaker I found from a quick Google search.

    http://www.hkvstore.com/phpmaker/doc/dynamicselect.htm

    It would seem possible to make one Selection List the dates for the past week or so and the dependent list the active pastures using a query similar to my initial suggestion. Pick a date from the first list and the second is dynamically adjusted to the active pastures for the selected day. Never having worked with the tool, I can't guarantee that it can be done, but I don't understand what would make it impossible.