Skip to Main Content
Return to Navigation

Using Oracle Trigger Information

This section discusses how to:

The triggers that are generated on the Oracle platform reference a function that PeopleSoft delivers to obtain the PS_OPRID. This function must be installed into the Oracle database schema for the PeopleSoft database prior to creating the trigger. This function can be installed by executing the following SQL as the PeopleSoft database owner ID:

$PS_HOME\scripts\getpsoprid.sql

Using Oracle Trigger Syntax

This example shows the Oracle trigger syntax.

drop function GET_PS_OPRID
/
create function GET_PS_OPRID (v_client_info VARCHAR2 )
        return VARCHAR2 is
        i integer;
/* Title: GET_PS_OPRID                                     */
/* Purpose: Retrieves the operator id (OPRID)              */
/*          from a VARCHAR2 comma separated field          */
/*          of the format 'OPRID,OS_USER,MACHINE'          */
/*          If no OPRID is found, it returns '!NoOPRID'    */
/* Limitations: (any grants, privileges, etc)              */
/* Who: PeopleSoft Inc.                                    */
/* Date: 2000-04-07                                        */
begin
        if ( length(v_client_info) IS NULL ) then
                return('!NoOPRID');
        end if;
        if ( substr(v_client_info,1,1) = ',' ) then
                return('!NoOPRID');
        end if;
        i := 1;
        while ( (substr(v_client_info,i,1)) <> ',' and i < 10) loop
                i := i + 1;
        end loop;
        if ( i > 9 ) then
                return('!NoOPRID');
        else
                i := i - 1;
                return (substr (v_client_info, 1, i));
        end if;
end GET_PS_OPRID;
/
grant execute on GET_PS_OPRID to public
/
/* If Transaction is an Insert Or Update               */
/*    Capture After Values                             */
/* If Transaction is a Delete or Update                */
/*    Capture Before Values                            */
CREATE OR REPLACE TRIGGER PS_ABSENCE_HIST_TR
AFTER INSERT OR UPDATE OR DELETE ON PS_ABSENCE_HIST
FOR EACH ROW
DECLARE
        V_AUDIT_OPRID VARCHAR2(64);
BEGIN
    DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);
  IF :OLD.EMPLID IS NULL
  THEN
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE                                        ,
                'A'                                            ,
                :NEW.EMPLID                                    ,
                :NEW.ABSENCE_TYPE                              ,
                :NEW.BEGIN_DT                                  ,
                :NEW.RETURN_DT                                 ,
                :NEW.DURATION_DAYS                             ,
                :NEW.DURATION_HOURS                            ,
                :NEW.REASON                                    ,
                :NEW.PAID_UNPAID                               ,
                :NEW.EMPLOYER_APPROVED
                );
  ELSE
      IF :NEW.EMPLID IS NULL
      THEN
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE                                    ,
                'D'                                        ,
                :OLD.EMPLID                                ,
                :OLD.ABSENCE_TYPE                          ,
                :OLD.BEGIN_DT                              ,
                :OLD.RETURN_DT                             ,
                :OLD.DURATION_DAYS                         ,
                :OLD.DURATION_HOURS                        ,
                :OLD.REASON                                ,
                :OLD.PAID_UNPAID                           ,
                :OLD.EMPLOYER_APPROVED
                );
      ELSE
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE                                    ,
                'K'                                        ,
                :OLD.EMPLID                                ,
                :OLD.ABSENCE_TYPE                          ,
                :OLD.BEGIN_DT                              ,
                :OLD.RETURN_DT                             ,
                :OLD.DURATION_DAYS                         ,
                :OLD.DURATION_HOURS                        ,
                :OLD.REASON                                ,
                :OLD.PAID_UNPAID                           ,
                :OLD.EMPLOYER_APPROVED
                );
        INSERT INTO PS_AUDIT_ABSENCE
        VALUES (
                GET_PS_OPRID(V_AUDIT_OPRID) ,
                SYSDATE                                    ,
                'N'                                        ,
                :NEW.EMPLID                                ,
                :NEW.ABSENCE_TYPE                          ,
                :NEW.BEGIN_DT                              ,
                :NEW.RETURN_DT                             ,
                :NEW.DURATION_DAYS                         ,
                :NEW.DURATION_HOURS                        ,
                :NEW.REASON                                ,
                :NEW.PAID_UNPAID                           ,
                :NEW.EMPLOYER_APPROVED
                );
      END IF;
  END IF;
END PS_ABSENCE_HIST_TR;
/

Maintaining Oracle Triggers

The following command may be helpful with triggers.

List All Triggers in a Database

To list triggers:

SELECT TRIGGERNAME FROM USER_TRIGGERS; 

Executed from Schema_owner_id

SELECT TRIGGERNAME FROM ALL_TRIGGERS; 

Executed from SYSTEM

The following data dictionary views reveal information about triggers:

  • USER_TRIGGERS

  •  

    SQL> descr user_triggers;
 Name                                 Null?          Type
 -------------------------------      --------       ----
 TRIGGER_NAME                         NOT NULL       VARCHAR2(30)
 TRIGGER_TYPE                                        VARCHAR2(16)
 TRIGGERING_EVENT                                    VARCHAR2(26)
 TABLE_OWNER                          NOT NULL       VARCHAR2(30)
 TABLE_NAME                           NOT NULL       VARCHAR2(30)
 REFERENCING_NAMES                                   VARCHAR2(87)
 WHEN_CLAUSE                                         VARCHAR2(4000)
 STATUS                                              VARCHAR2(8)
 DESCRIPTION                                         VARCHAR2(4000)
 TRIGGER_BODY                                        LONG

ALL_TRIGGERS

    SQL> desc all_triggers;
 Name                                 Null?          Type
 -------------------------------      --------       ----
 OWNER                                NOT NULL       VARCHAR2(30)
 TRIGGER_NAME                         NOT NULL       VARCHAR2(30)
 TRIGGER_TYPE                                        VARCHAR2(16)
 TRIGGERING_EVENT                                    VARCHAR2(26)
 TABLE_OWNER                          NOT NULL       VARCHAR2(30)
 TABLE_NAME                           NOT NULL       VARCHAR2(30)
 REFERENCING_NAMES                                   VARCHAR2(87)
 WHEN_CLAUSE                                         VARCHAR2(4000)
 STATUS                                              VARCHAR2(8)
 DESCRIPTION                                         VARCHAR2(4000)
 TRIGGER_BODY                                        LONG

USER_TRIGGERS

    SQL> descr user_triggers;
 Name                                 Null?          Type
 -------------------------------      --------       ----
 OWNER                                NOT NULL       VARCHAR2(30)
 TRIGGER_NAME                         NOT NULL       VARCHAR2(30)
 TRIGGER_TYPE                                        VARCHAR2(16)
 TRIGGERING_EVENT                                    VARCHAR2(26)
 TABLE_OWNER                          NOT NULL       VARCHAR2(30)
 TABLE_NAME                           NOT NULL       VARCHAR2(30)
 REFERENCING_NAMES                                   VARCHAR2(87)
 WHEN_CLAUSE                                         VARCHAR2(4000)
 STATUS                                              VARCHAR2(8)
 DESCRIPTION                                         VARCHAR2(4000)
 TRIGGER_BODY                                        LONG

The new column, BASE_OBJECT_TYPE, specifies whether the trigger is based on DATABASE, SCHEMA, table, or view. The old column, TABLE_NAME, is null if the base object is not table or view.

The column ACTION_TYPE specifies whether the trigger is a call type trigger or a PL/SQL trigger.

The column TRIGGER_TYPE includes two additional values: BEFORE EVENT and AFTER EVENT, which are applicable only to system events.

The column TRIGGERING_EVENT includes all system and DML events.

List the Trigger Definition

To list the trigger definition:

Select Trigger_Name,  Trigger_Body  from USER_TRIGGERS
 where Trigger_name=trigger_name;

List Trigger Information

To list trigger information:

Select Trigger_Name, Trigger_Type, Triggering_Event, Table_Owner,
Table_Name, Referencing_Names,When_Clause, Status, Description, 
Trigger_Body from USER_TRIGGERS where Trigger_name=trigger_name;

To Remove a Trigger

To remove a trigger:

drop trigger TRIGGERNAME

To Modify an Existing Trigger

On Oracle, to explicitly alter a trigger definition, use the CREATE OR REPLACE option. See a full explanation in the Oracle SQL Reference (CREATE TRIGGER).

To Disable a Trigger

By default, triggers are enabled when they're first created. Disable a trigger by using the ALTER TRIGGER statement with the DISABLE option.

For example, to disable the trigger named REORDER of the INVENTORY table, enter the following statement:

ALTER TRIGGER Reorder DISABLE;

All triggers that are associated with a table can be disabled with one statement by using the ALTER TABLE statement with the DISABLE clause and the ALL TRIGGERS option. For example, to disable all triggers that are defined for the INVENTORY table, enter the following statement:

ALTER TABLE Inventory
    DISABLE ALL TRIGGERS;