Ok,
thought there could be some well known troubles and
thought this would be too much information to help me.
Here is the code.
Thanks
Peter
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(35)
INSERT_DATE DATE
INSERT_INITIALS VARCHAR2(5)
UPDATE_DATE DATE
UPDATE_INITIALS VARCHAR2(5)
NT_LANGUAGE_ID NOT NULL NUMBER(10)
CO_GRP_COMP_ID NOT NULL NUMBER(10)
OFFICE_NO NUMBER(6)
IS_GRP_COMP_DEFAULT NOT NULL VARCHAR2(5)
TRIGGER NT_Language_InsUp BEFORE UPDATE OR INSERT ON NT_LANGUAGE
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
-- ###############################################################################################################
--
--
- Description: Trigger which fires on insert and update
- Set the insert_initials, insert_date, update_initials, update_date.
- Is used for each table containing these fields within the project coast.
- Reads the input from the temporary table user_attribute, where the initials and difference
- to UTC (in sec) are set from WebObjects before doing any writes to the database.
- Initials are converted to upper case.
- Uses the function SYSDATE_WITH_SERVER_TIMEZONE instead of sysdate !
--
--
- Modifiaction: -
--
- Date: 18.10.2002
- Author: PJE
- Version: 001
--
- ###############################################################################################################
DECLARE
initials VARCHAR2(5);
differenceToUTC_InSec NUMBER(10);
- the input comes from the temporary table USER_ATTRIBUTE
CURSOR userAttribute_cursor IS SELECT USER_INITIALS, USER_DELTA_TO_UTC FROM USER_ATTRIBUTE;
BEGIN
OPEN userAttribute_cursor;
FETCH userAttribute_cursor INTO initials, differenceToUTC_InSec;
- there should be never more than one entry
IF userAttribute_cursor%ROWCOUNT > 1 THEN
raise_application_error(-20001, 'There is more than one entry in the temporay table USER_ATTRIBUTES. Please call your admin.', TRUE);
END IF;
CLOSE userAttribute_cursor;
- let's save the initials always in upper case
initials := UPPER(initials);
- Code for INSERTING -----------------------------------------------------
IF INSERTING THEN
- inserting will set insert_initials and insert_date
:NEW.INSERT_INITIALS := initials;
- let's do the correction for the time (different timezone)
IF differenceToUTC_InSec IS NULL THEN
:NEW.INSERT_DATE := SYSDATE_WITH_SERVER_TIMEZONE();
ELSE
- sysdate + 1 adds one day, one day is 24*60*60 = 86400 sec
:NEW.INSERT_DATE := SYSDATE_WITH_SERVER_TIMEZONE() + (differenceToUTC_InSec / 86400);
END IF;
- Code for UPDATING ------------------------------------------------------
ELSIF UPDATING THEN
- updating will set update_initials and update_date
:NEW.UPDATE_INITIALS := initials;
- let's do the correction for the time (different timezone)
IF differenceToUTC_InSec IS NULL THEN
:NEW.UPDATE_DATE := SYSDATE_WITH_SERVER_TIMEZONE();
ELSE
- sysdate + 1 adds one day, one day is 24*60*60 = 86400 sec
:NEW.UPDATE_DATE := SYSDATE_WITH_SERVER_TIMEZONE() + (differenceToUTC_InSec / 86400);
END IF;
END IF; -- INSERTING, UPDATING
END;
Received on Thu May 12 2005 - 11:15:50 CDT
