Using Oracle Trigger Information
This section discusses using Oracle trigger information.
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.sqlThis example shows the Oracle trigger syntax.
/* 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) */
*/
drop function GET_PS_OPRID
/
create function GET_PS_OPRID (v_client_info VARCHAR2 )
return VARCHAR2 is
i integer;
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 < 32) loop
i := i + 1;
end loop;
if ( i > 31 ) 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;
/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 LONGALL_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
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 TRIGGERNAMETo 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;