DDL Triggers

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:
1.System events
.....a. Database startup and shutdown
.....b. Server error message events
2.User 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
AFTER SUSPEND

Example:

CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA

DECLARE
oper ddl_log.operation%TYPE;
BEGIN
INSERT INTO ddl_log
SELECT ora_sysevent, ora_dict_obj_owner,
ora_dict_obj_name, NULL, USER, SYSDATE
FROM dual;
END bcs_trigger;
/

DDL Triggers in SQL Server 2005:

Following are few of the events supported in SQL Server 2005:

Create_Table
Alter_Table
Drop_Table
Create_View
Alter_View
Drop_View
Create_Synonym
Drop_Synonym
Create_Function
Alter_Function
Drop_Function
Create_Procedure
Alter_Procedure
Drop_Procedure

Example:

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK
;

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.