A trigger is a block of PL/SQL or Java or C code that executes or fires when a particular event occurs within database.
Triggers are useful for implementing code that must be executed on regular basis due to a pre-defined event. By utilizing, triggers, scheduling and data integrity problems can be eliminated because the trigger will be fired whenever the triggering event occurs. You need not remember to schedule or code an activity to perform the logic in the trigger.
There are various events on which a trigger can be written, such as:
.....a. Database startup and shutdown
.....b. Server error message events
.....a. User logon and logoff
.....b. DDL statements (CREATE, ALTER, and DROP)
.....c. DML statements (INSERT, DELETE, and UPDATE)
Here we would be concentrating on triggers invoked by DDL statements.
Being a DBA, you are monitoring a database and suddenly you realize that there is a particular table, which is occupying lots of space. The table is not a part of application tables and you don’t know who had created it.
Your application is working fine. One fine day, users start complaining that the application is not working. After searching for hours, you come to know someone had altered a table. If you had prevented alter for the user, this wouldn’t have happened.
In such scenario, DDL triggers can be used. Use DDL triggers when you want to do the following:
• You want to prevent certain changes to your database schema.
• You want something to occur in the database in response to a change in your database schema.
• You want to record changes or events in the database schema.
DDL Triggers are supported in Oracle and SQL Server 2005 but not in DB2.
DDL Triggers in Oracle:
Following is the list of the events supported in Oracle:
BEFORE / AFTER ALTER
BEFORE / AFTER CREATE
BEFORE / AFTER DROP
BEFORE / AFTER RENAME
BEFORE / AFTER ANALYZE
BEFORE / AFTER ASSOCIATE STATISTICS
BEFORE / AFTER DISASSOCIATE STATISTICS
BEFORE / AFTER AUDIT
BEFORE / AFTER NOAUDIT
BEFORE / AFTER COMMENT
BEFORE / AFTER DDL
BEFORE / AFTER GRANT
BEFORE / AFTER REVOKE
BEFORE / AFTER TRUNCATE
CREATE OR REPLACE TRIGGER bcs_trigger
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, NULL, USER, SYSDATE
DDL Triggers in SQL Server 2005:
Following are few of the events supported in SQL Server 2005:
CREATE TRIGGER safety
FOR DROP_TABLE, ALTER_TABLE
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
Using DDL Triggers, you can prevent or log changes to your database. As a DBA or database developer, it may be important to know if and when something in your database has changed. In some cases, you may even want to prevent changes by any user altogether.
Using DDL Triggers, you may have naming conventions in your database. DDL triggers can enforce them by preventing improperly named tables, views, stored procedures, and even table columns.
Using DDL Triggers, you may want to perform other actions in response to a DDL event.
As a DBA or database developer, it may be important to respond to changes in a database. DDL triggers allow you to respond to and prevent changes altogether.