/*****************************************************************************
*
* Filename         - $Workfile:   tiub_timezone_offset.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema:
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

-- File renamed from tiub_timezone_offset.sql - Dave Banks - FALCP/CA/1771 - 8th March 2006

PROMPT CREATE OR REPLACE TRIGGER tiub_timezone_offset

CREATE OR REPLACE TRIGGER tiub_timezone_offset
BEFORE INSERT OR UPDATE OF offset, utc_offset ON TIMEZONE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  utc_offset_difference NUMBER;
BEGIN
  utc_offset_difference := to_number (config.get_value ('UTC_OFFSET_DIFF'));

  IF inserting THEN
    IF :new.offset is null THEN
      :new.offset := (:new.utc_offset - utc_offset_difference) / 60;
    ELSIF :new.utc_offset is null THEN
      :new.utc_offset := (:new.offset * 60) + utc_offset_difference;
    ELSE
      raise_application_error (
          -20000,
          'You cannot insert both offset  at the same time.');
    END IF;
  ELSIF updating THEN
    IF :new.offset != :old.offset AND :new.utc_offset != :old.utc_offset THEN
      raise_application_error (
          -20000,
          'You cannot update both offset  at the same time.');
    ELSIF :new.offset != :old.offset THEN
      :new.utc_offset := (:new.offset * 60) + utc_offset_difference;
    ELSIF :new.utc_offset != :old.utc_offset THEN
      :new.offset := (:new.utc_offset - utc_offset_difference) / 60;
    ELSE
      null; -- do nothing
    END IF;
  END IF;
END;
/


