Oracle Tips by Burleson

Alteration of Triggers

As was stated in the “Creation of Database Triggers” section preceding, the CREATE command has the OR REPLACE option to allow a trigger to be re-created without being dropped. Also availabe is the COMPILE [DEBUG] option that allows recompilation and debug of a trigger that has become invalidated. To alter the contents of a trigger, this create or replace option is used. A trigger has one of two possible states: ENABLED or DISABLED. The ALTER TRIGGER command is limited in functionality:

ALTER TRIGGER [schema.]trigger_name ENABLE|DISABLE|COMPILE [DEBUG];      

One limit on the usefulness of the ALTER TABLE in either disabling or enabling triggers is that it is an all-or-nothing proposition. It is better to use the ALTER TRIGGER command, unless you want all of the triggers on the table enabled or disabled at one time.     

The DEBUG option instructs the PL/SQL compiler to generate and store the code for use by the PL/SQL debugger.

Dropping a Trigger

Triggers are dropped using the DROP TRIGGER command:

See Code Depot

Tip  

Give careful consideration to all triggers created after Oracle8. Ask whether its function could be better accomplished with a method. If so, use a method. Check for trigger dependencies before dropping a trigger or significantly altering a trigger’s actions.

Administration of Functions and Procedures

New in Oracle8 was the advance typing that allows a PL/SQL table in functions and procedures to be multidimensional. In contrast, in Oracle7, a PL/SQL table had to be scalar (a single datatype). Oracle8 also offers object support and external procedure and function calls.      

Functions and procedures under Oracle7, Oracle8, Oracle8i, and Oracle9i are virtually identical. The major difference between functions and procedures is that functions always return a single value, whereas procedures may return one, many, or no values. This leads to a second difference: The procedure can use the OUT and IN OUT arguments in the CREATE command, but the function can’t. In fact, the function doesn’t have to specify the IN argument since input to a function is required. Structurally, procedures and functions didn’t change between versions Oracle7 and Oracle8; however, in Oracle8i, the AUTHID clause (invoker_rights_clause) and the DETERMINISTIC and PARALLEL_ ENABLE keywords were added, as well as the capability to call C and Java objects. In Oracle9i, the capability was added to return PL/SQL tables as either aggregates (must be returned to a PL/SQL table) or PIPELINED (returns individual values).

www.oracle-script.com