Home » SQL & PL/SQL » SQL & PL/SQL » how to use PL/SQL in a before insert trigger to check a value.
| how to use PL/SQL in a before insert trigger to check a value. [message #623738] |
Sat, 13 September 2014 11:46  |
 |
mattfriend
Messages: 12
Registered: September 2014
|
Junior Member |
|
|
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:
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:
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
Lalit : Formatted the code and added code tags.
[Updated on: Sun, 14 September 2014 03:10] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: how to use PL/SQL in a before insert trigger to check a value. [message #623742 is a reply to message #623741] |
Sat, 13 September 2014 13:08  |
 |
Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here are my thoughts (you may, but don't have to agree with me).
First of all: how do you enter data into the database? I guess users don't do that directly, using SQL*Plus or any other tool which directly communicates with the database. I suppose that it is some kind of an application. Let's just suppose that it is a Forms application.
If you code a database trigger, users won't be too happy because they will already enter all data, push the "Save" button and be surprised by an error message raised by the database.
Therefore, why wouldn't you do act proactively? Forms offer "list of values" which is, basically, result of a SELECT statement. As you want to restrict number of pastures (from their maximum number of some 1500 to only allowed ones (~60, as you said)), you'd attach list of values to the PASTURE item and make sure that users can NOT enter any other pasture (except the ones returned by the list of values); if they try to do that, application raises an error and says "no, you can't enter that pasture - select one from the list of values". Forms do that itself (by setting one of item's properties); you might do that in a different manner in a tool you use.
List of values query would be similar to the one you already wrote; however, instead of referencing SYSDATE (and subtracting one, two ... who-knows-how-many days from it - which is most probably wrong as you can't think that way) you'd reference DATE_FED user enters on the screen, before navigating to the PASTURE item. Something like this:
select
g.pasture
from grazing_records g
where g.date_in <= :application.date_fed_item
and ( g.date_out is null -- animals still there
or g.date_out = :application.date_fed_item - 1 -- animals left "yesterday"
)
order by g.pasture
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 23:05:45 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
