OracleVillage

How to call external procedure within trigger body?

The call statement can be used to call an external procedure within a trigger body. The external procedure called using the CALL statement can be either a PL/SQL stored procedure or a Java procedure. The statement will be execute if the condition associated with the trigger evaluates to true. The following code shows an example of trigger that uses the CALL statement to call the log_proc procedure:

Before creating the database trigger you must have ADMINISTER DATABASE TRIGGER privilege.

CONNECT system/manager
GRANT ADMINISTER DATABASE TRIGGER TO scott;
CONNECT scott/tiger

CREATE OR REPLACE TRIGGER test_logoff
BEFORE LOGOFF ON DATABASE
CALL log_proc
/

CREATE OR REPLACE PROCEDURE log_proc
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent,
ora_dict_obj_owner,
ora_dict_obj_name,
'Not Allowed',
USER,
SYSDATE
FROM DUAL;

COMMIT;
END log_proc;
/
Example 2:
Assume you have defined a procedure salary_check, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger salary_check as follows:

CREATE TRIGGER salary_check
BEFORE INSERT OR UPDATE OF salary, job_id ON employees
FOR EACH ROW
WHEN (new.job_id <> 'AD_VP')
CALL check_sal(:new.job_id, :new.salary, :new.last_name);


The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.