| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Freeware: Oracle Database Y2K Protection (Part I)
Oracle Database Y2K Protection
Triggers Generators
By Carl Cribbs
Westinghouse- SRS
Administrative
NOTE: Text enclosed between "<" and ">" are meant
to represent generic information.
For example: <date column> is meant to represent
date columns in general- NOT THE SPECIFIED "DATE
COLUMN."
Synopsis
I developed PL/SQL code that generates or modifies
PL/SQL "BEFORE EACH ROW" triggers to protect
database date columns from Y2K non-compliant date
input (from all sources) into the database. A
function is imbedded in the triggers that uses the
"RR" year formatted date conversion. For each
table with at least one date column and with
INSERT/UPDATE/DELETE trigger(s), my code inserts
date conversion code into the existing trigger(s).
For INSERT/UPDATE not in a trigger(s), my code
creates a trigger for the absent DML command(s).
Designed to be: Transferable to other servers with minimum effort; A uniform and consistent problem solution with easy implementation, testing, and configuration management.
No need to manually code and edit SQL trigger
files: Modifies existing triggers;
Creates needed triggers; Self documented (output
comments with code); SQL files
configuration management ready. Can customize the:
Date conversion function; Code modifications for
the trigger; Universal lookup/key.
Problem
Due to legacy software, we needed to protect database dates columns from Y2K non-compliant dates. That is, to control date data coming into the database. With legacy software of SQL*FORMS 3.0, SQLPLUS, and Pro*FORTRAN, the solution had to be applicable for multiple input sources. Next, it would have to work for future unknown application, upgrades, etc. Additionally, we needed uniform and consistent problem solution for ease of implementation, testing, and configuration management. Then, it would have to be transferable to other servers with minimum effort. With a four month Y2K conversion deadline for our entire Laboratory Information Management System (LIMS), the need was immediate. All this had to be resolved in our legacy software environment.
Software Environment and Software Restrictions
Our in-house LIMS uses OpenVMS V6.2 and VT terminals (or VT emulators) for the system platform. The Oracle software consists of: Oracle7 Server Release 7.1.5.2.3; SQL*Plus Release 3.1.3.5.1; PL/SQL Release 2.1.5.2.0. All our dates are greater than 1980 but less than 2010. Therefore, the most encompassing solution pointed to was triggers.
Triggers and Tables with Dates
To give complete coverage for all date fields within our software context, the approach I used was to both modify existing triggers and create new triggers to contain date conversion code. Since can only change a column's value in "BEFORE" triggers and want the Y2K conversion to affect each record inserted or updated, all triggers upgraded or created are/will be "BEFORE EACH ROW" triggers associated with tables that have at least one date field. Specifically, generates code like:
:NEW.timestamp :=
rr_date_conversion(:NEW.timestamp);
Using PL/SQL code that generates PL/SQL triggers
that run from SQL command files, I developed a
module to upgrade existing triggers and an another
module to create needed triggers. This gave full
coverage for each table with at least one date
field:
+----------------------------+--------------------
-----+
|Existing BEFORE/EACH ROW |
Type of BEFORE/EACH ROW |
|Trigger for a Table |
Trigger Generated |
|----------------------------+--------------------
-----|
| INSERT OR UPDATE |
None |
| INSERT OR UPDATE OR DELETE |
None |
| INSERT |
UPDATE |
| INSERT OR DELETE |
UPDATE |
| UPDATE |
INSERT |
| UPDATE OR DELETE |
INSERT |
| None |
INSERT OR UPDATE |
+----------------------------+--------------------
-----+
50% of the triggers are "BEFORE EACH ROW"
triggers. My pre-Y2K conversion trigger break out
base on triggering events is:
i. 10% contain "INSERT OR UPDATE" (most are
"INSERT OR UPDATE OR
DELETE")
ii. 10% contain "INSERT" (most are "INSERT OR
DELETE")
iii. 30% contain "UPDATE" or "UPDATE OF" (most
are "UPDATE OR DELETE")
These triggers were upgraded. All created or
upgraded triggers are configured to call the date
conversion function.
Solution
The created and upgraded triggers use the standard
date conversion function
"rr_date_conversion." Using the "RR" date format,
the date conversion function is based on the last
two digits of the year. Each year with its last
two digits less than 50 is converted to the 21st
century. Each year with its last two digits
greater than or equal to 50 is converted to the
20th century. For all new and modified triggers,
the date conversion syntax is " (also see
Attachment Number 1):
<owner>.rr_date_conversion
(
yydate IN DATE /* date to convert to Y2K
compliance */
)
RETURN DATE IS
rrdate DATE; /* Y2K compliant date to
return */
TO_DATE
(
TO_CHAR (yydate,
'DD-MM-YY HH:MI:SS' ),
'DD-MM-RR HH:MI:SS'
)
INTO
rrdate
FROM DUAL;
IF :NEW.<date column> IS NOT NULL
THEN
:NEW.<date column> :=
rr_date_conversion( :NEW.<date
column> );
END IF;
<repeats IF THEN block for each date
column>
The file "Y2K_TRIGGER_GENERATOR.SQL" (see
Attachment Number 2) contains the
PL/SQL "Generator" code. By spooling created
triggers to "Y2K_DATE_COLUMN_NEW_TRIG_CODE.SQL," a
command file of new/created triggers is
created. Additionally, "DROP TRIGGER" commands are
spool to
Y2K_DROP_RR_TRIGGERS.SQL." (As shown in the above
table, triggers are not generated for existing
"BEFORE INSERT." and/or " BEFORE UPDATE."
triggers.) The basic structure of the created
trigger is:
<Header information/comments>
BEGIN
<Date conversion code>
END;
Additionally, all new/created trigger names
consists of "RR_" concatenated to
the beginning of the trigger's table name. The
creating of triggers methodology
is basically a subset of the upgrade of existing
triggers.
The file "Y2K_EXISTING_TRIGGER_UPGRADE.SQL" (see
Attachment Number 3) contains
the PL/SQL code for "Upgrade." By spooling
upgraded triggers to
"Y2K_TRIGGER_UPGRADE_CODE.SQL," it generated the
code that inserts date conversion code into
existing triggers. The basic structure of the
original syntax in "SYS.DBA_TRIGGERS.TRIGGER_BODY"
is:
BEGIN
<Original code- less BEGIN/END>
END;
The basic structure of the upgraded code is:
BEGIN
<Header information/comments>
BEGIN
<Date conversion code>
END;
BEGIN
<Original code- less BEGIN/END>
Basics of Code Logic
The basic logic used to generate the new triggers (PL/SQL code "Generator") is:
The basic logic used to update/upgrade old triggers (PL/SQL code for "Upgrade") is:
This reasonably simple approach yielded the desired results and more.
Benefits
The process of protecting date fields has been automated. Since the SQL files can be run from the SQL*Plus prompt, no special tools are required- nothing to buy. Moreover, there is no need to edit SQL trigger files: Modifies existing triggers; Creates needed triggers; Self documented (output comments with code); SQL files configuration management ready. Available now requiring minimal modification for usage.
Required Customization/Modification
Since this was developed with PL/SQL Release
2.1.5.2.0 and SQL*Plus Release 3.1.3.5.1,each line
is prefixed with a tab. Since the original code
and the Y2K conversion are imbedded into two
separate inner blocks and leading blanks are
suppressed/trimmed when using
"DBMS_OUTPUT.PUT_LINE" commands, the tab prefix
maintains a semblance of the original indentation.
This "tab" code can be removed, commented out, or
replaced with spacing when using later PL/SQL
versions. Next, if already used with objects, may
want to replace the "RR_" (newly created trigger)
prefix.
Finally, for the provided SQL files to work, the trigger owner must be inserted in various WHERE clauses:
Possible Alternatives and Enhancements
With your unique date requirement, you can
customize "RR_DATE_CONVERSION" for the desired
date handling (e.g. for the years 1910 through
2009). If you can determine uniform keying
information, the concept of creating
triggers/modifying existing triggers can be
expanded. (For me it was:
SYS.DBA_TAB_COLUMNS.DATA_TYPE = "DATE.") "WHERE"
logic can be set up for fields with the
same/similar names (e.g. tables with field names
of "USER_ID"). In addition to any type of
triggering event and/or trigger type, you can look
for values in the columns of the SYS.DBA_TRIGGERS
or SYS.DBA_TRIGGER_COLS. Also, occurrences of an
object in a trigger (e.g. a synonym in
SYS.DBA_TRIGGERS "WHEN_CLAUSE" field).
Additionally, the "WHEN" clause is a prime
candidate to control data coming into the
database. If you have something consistent to
search for in all the needed triggers, you can
make uniform modifications to the trigger codes
(found in: SYS.DBA_TRIGGERS.TRIGGER_BODY).
Any one want to integrate these concepts into a GUI product?
Conclusion
Database now has blanket input Y2K protection that
meets our requirements. Without extensive editing
work by developers, all or selected triggers can
be "electronically" modified. Additionally,
"electronically" created triggers when the need
existed. The concept can be expanded well beyond
Y2K protection.
Think in terms of
automating problem solutions
Attachment Number 1
RR_DATE_CONVERSION.SQL
/*________________________________________________ ______________________________ Function : rr_date_conversion Author : Carl Cribbs Date Created : 02-Mar-1999 Description : Converts dates to be Y2K compliant Restrictions : Any year with it last two digits less than 50 is treated as a 21st century date. Any year with it last two less greater than or equal to 50 is treated as a 20th century date. Parameters : yydate - the date to convert Return Value : rrdate - Y2K compliant date toreturn
____________________________*/
(
yydate IN DATE /* date to convert to Y2K
compliance */
)
RETURN DATE IS
rrdate DATE; /* Y2K compliant date to
return */
/*________________________________________________ ______________________________
Name / Date / Comment
/ /
/ /
__________________________________________________
____________________________*/
If last two digits of date to convert is less than 50, converts
to a 21st century date. Otherwise, converts to
a 20th century date.
*/
SELECT
TO_DATE ( TO_CHAR ( yydate, 'DD-MM-YY
HH:MI:SS' ),'DD-MM-RR HH:MI:SS' )
INTO
rrdate
FROM DUAL;
RETURN (rrdate);
/*
Attachment Number 2
Y2K_TRIGGER_GENERATOR.SQL
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 80
SET SERVEROUTPUT On SIZE 1000000
SET SHOWMODE OFF
SET TERM OFF
-- -- File : Y2K_TRIGGER_GENERATOR.SQL -- Purpose : Each table's trigger will convert date data (for each -- date field) to the year range of1950 through 2049.
-- Author : Carl Cribbs -- Date Created : 01-APR-1999 -- Restrictions : a. Only for tables with date fields. -- b. The date conversion function that is called, -- "rr_date_conversion," converts: -- i. All years with it last two digits less than 50 to -- a 21st century date. -- ii. All years with it last two digits greater than or -- equal to 50 to a 20th century date. -- c. Will not generate triggers for existing "BEFORE INSERT" -- and/or "BEFORE UPDATE"triggers:
-- +----------------------------+-------------------- -----+ -- |Existing BEFORE/EACH ROW | Type of BEFORE/EACH ROW | -- |Trigger for a Table | Trigger Generated | -- |----------------------------+-------------------- -----| -- | INSERT OR UPDATE | None | -- | INSERT OR UPDATE OR DELETE | None | -- | INSERT | UPDATE | -- | INSERT OR DELETE | UPDATE | -- | UPDATE | INSERT | -- | UPDATE OR DELETE | INSERT | -- | None | INSERT OR UPDATE | -- +----------------------------+-------------------- -----+ -- -------------------------------------------------- ------------------------------
-- -------------------------------------------------- ------------------------------ -------------------------------------------------- ------------------------------ --
-- -- List of tables with date column(s) -- -- Each "BEFORE EACH ROW" trigger with both
-- having a trigger generated).
--
--------------------------------------------------
------------------------------
CURSOR date_tables IS
SELECT DISTINCT
owner, table_name
FROM
sys.dba_tab_columns
WHERE
owner = '<OWNER>'
AND
data_type = 'DATE'
MINUS
SELECT
table_owner, table_name
FROM
sys.dba_triggers
WHERE
table_owner = '<OWNER>'
AND
trigger_type = 'BEFORE EACH ROW'
AND
triggering_event LIKE '%UPDATE%INSERT%'
MINUS
SELECT
table_owner, table_name
FROM
sys.dba_triggers
WHERE
table_owner = '<OWNER>'
AND
trigger_type = 'BEFORE EACH ROW'
AND
triggering_event LIKE '%INSERT%UPDATE%'
ORDER BY 1, 2;
--------------------------------------------------
------------------------------
--
--------------------------------------------------
------------------------------
CURSOR trigger_for_table
(
table_with_date
sys.dba_triggers.table_name%TYPE
) IS
SELECT
triggering_event
FROM
sys.dba_triggers
WHERE
table_name = table_with_date
AND
table_owner = '<OWNER>'
AND
owner = table_owner
AND
trigger_type = 'BEFORE EACH ROW';
--------------------------------------------------
------------------------------
--
-- List of dates for each table
--
--------------------------------------------------
------------------------------
CURSOR build_date_columns
(
table_date_columns
sys.dba_tab_columns.table_name%TYPE
) IS
SELECT
column_name
FROM
sys.dba_tab_columns
WHERE
owner = '<OWNER>'
AND
table_name = table_date_columns
AND
data_type = 'DATE'
ORDER BY column_name;
--------------------------------------------------
------------------------------
--------------------------------------------------
------------------------------
--
-- Variables
--
--------------------------------------------------
------------------------------
--
-- var_filtered_triggering_event
-- Triggering event information string.
Receives a trigger's
-- triggering event(s) text. Then has
everything removed except
-- "INSERT" and/or "UPDATE."
--
-- var_triggering_event_string
-- After being filtered, determines if can
build an INSERT trigger or
-- an UPDATE trigger or cannot build a
trigger.
--
--------------------------------------------------
------------------------------
var_filtered_triggering_event
sys.dba_triggers.triggering_event%TYPE;
var_triggering_event_string
VARCHAR2(12);
--------------------------------------------------
------------------------------
--------------------------------------------------
------------------------------
-- -------------------------------------------------- ------------------------------ -------------------------------------------------- ------------------------------
BEGIN
-- (Each table has at least one date column.) -- -------------------------------------------------- ------------------------------
FOR trig_build
IN date_tables
LOOP
DBMS_OUTPUT.PUT_LINE (
'ON ' ||
trig_build.owner || '.'
|| LOWER(trig_build.table_name) );
DBMS_OUTPUT.PUT_LINE ( 'FOR EACH ROW' );
DBMS_OUTPUT.PUT_LINE ( '/*' );
DBMS_OUTPUT.PUT_LINE (
'_________________________________________________
________'
|| '_______________________' );
DBMS_OUTPUT.PUT_LINE ( '|' );
DBMS_OUTPUT.PUT_LINE (
'| Trigger : RR_' || REPLACE(
trig_build.table_name, 'DWPF_' ));
DBMS_OUTPUT.PUT_LINE (
'| Author : Carl Cribbs' );
DBMS_OUTPUT.PUT_LINE (
'| Date Created: ' || to_char(
SYSDATE, 'DD-MON-YYYY' ) );
DBMS_OUTPUT.PUT_LINE (
'| Description : For table ' ||
trig_build.table_name || '.' );
DBMS_OUTPUT.PUT_LINE (
'| When an insert occurs
on a record or an update,' );
DBMS_OUTPUT.PUT_LINE (
'| occurs on the date
column, converts the date ' );
DBMS_OUTPUT.PUT_LINE (
'| to be Y2K compliant.'
);
DBMS_OUTPUT.PUT_LINE (
'| Restrictions: Any year with it last
two digits less than 50 is');
DBMS_OUTPUT.PUT_LINE (
'| converted to a 21st
century date. '
|| 'Any year with it' );
DBMS_OUTPUT.PUT_LINE (
'| last two digits
greater than or equal to 50 is' );
DBMS_OUTPUT.PUT_LINE (
'| converted to a 20th
century date.' );
DBMS_OUTPUT.PUT_LINE ( '|' );
DBMS_OUTPUT.PUT_LINE (
'_________________________________________________
________'
|| '_______________________' );
DBMS_OUTPUT.PUT_LINE (
'_________________________________________________
________'
|| '_______________________' );
DBMS_OUTPUT.PUT_LINE (
'| Name | Date | Comment' );
DBMS_OUTPUT.PUT_LINE (
'_________________________________________________
________'
|| '_______________________' );
DBMS_OUTPUT.PUT_LINE (
'| | |' );
DBMS_OUTPUT.PUT_LINE (
'| | |' );
DBMS_OUTPUT.PUT_LINE (
'| | |' );
DBMS_OUTPUT.PUT_LINE (
'_________________________________________________
________'
|| '_______________________' );
DBMS_OUTPUT.PUT_LINE ( '*/' );
DBMS_OUTPUT.PUT_LINE ( 'BEGIN' );
DBMS_OUTPUT.PUT_LINE ( '--' );
DBMS_OUTPUT.PUT_LINE (
'-- Convert date coming into database
to be Y2K complaint.' );
DBMS_OUTPUT.PUT_LINE ( '--' );
--------------------------------------------------
------------------------------
'_________________________________________________
________'
|| '_______________________' );
DBMS_OUTPUT.PUT_LINE ( '*/' );
DBMS_OUTPUT.PUT_LINE ( '/' );
ROLLBACK;
END IF;
END LOOP;
END;
.
/
--
--
SPOOL OFF
--
-- Execute the generated code to create the
triggers
--
@y2k_date_column_new_trig_code.sql
-- -- -------------------------------------------------- ------------------------------ -------------------------------------------------- ------------------------------ -- -- Remove/drop Y2K protection triggers -- (DROP TRIGGER commands save to
-- -------------------------------------------------- ------------------------------ --
-- -- -------------------------------------------------- ------------------------------ -- F O R D E B U G G I N G- -- cannot create triggers generator file if tablealready has triggers
-- -- @y2k_drop_rr_triggers.sql -------------------------------------------------- ------------------------------ -- --
 |
 |