Modifying Triggers

Contact Us or call 1-877-932-8228
Modifying Triggers

Modifying Triggers

A trigger can be created using the CREATE statement. As with other subprograms, a CREATE statement will fail if an object already exists. You can override this behavior by using the Oracle proprietary REPLACE keyword. This allows an existing subprogram (in this case a trigger) to be modified without having to drop it and then recreate it.

Code Sample:

Database-Triggers/Demos/ddl_create_trigger.sql
create or replace trigger creation_alert
after create on schema

begin
  dbms_output.put_line('Something has been created.');
end;
/

Code Explanation

This trigger will fire when an object is created within the user's schema. In this case, the trigger will simply output a message to the screen.

Code Sample:

Database-Triggers/Demos/ddl_modify_trigger.sql
create or replace trigger creation_alert
after create on schema
begin
 dbms_output.put_line(ORA_DICT_OBJ_TYPE
                   || ' '
                   || ORA_DICT_OBJ_NAME
                   || ' has been created.');
end;
/

Code Explanation

The trigger is modified using the OR REPLACE clause. The output will now include information about the type and name of the object created.

Next