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.

Dangers of Bind Variable

We have seen and heard many advantages of using bind variables in a query, one of which is doing hard parse when a query is executed for the first time and then reusing the plan created at the time of first execution.

But there is a catch in this. This is advantageous for less skewed data but if a column is heavily skewed, it becomes disadvantage.

Look at the example given below:

DROP TABLE t;

CREATE TABLE t
AS
SELECT 1 id, a.* FROM all_objects a;

UPDATE t SET id = 99 WHERE ROWNUM = 1;

CREATE INDEX t_idx ON t(id);

ANALYZE TABLE t COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL indexed COLUMNS SIZE 2;

variable n number;

exec :n := 1;

select * from t n_was_1 where id = :n;

Explain Plan from v$sql_plan

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS FULL

exec :n := 99;

select * from t n_was_99 where id = :n;

Explain Plan from v$sql_plan

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID

These plans are exactly similar to what we would expect it to be.

Now we execute same queries (using soft parse):

exec :n := 99;

select * from t n_was_1 where id = :n;

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS FULL

exec :n := 1;

select * from t n_was_99 where id = :n;

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID


As you can see, the queries have used the plan which was created at the time of hard parse.

Use hints like FIRST_ROWS(n)

Usually queries used for search fetch lots of records if proper search condition is not provided. If the query is fetching thousands of rows, that will be of no use for the end user. In such scenario, no of records should be limited by using ROWNUM <>/*+ FIRST_ROWS (n) */ should be used, where n is no of rows required.


Note: Hint /*+ FIRST_ROWS */ does not know the scope of the query and generally favours index access over full-table scans. Therefore /*+ FIRST_ROWS (n) */ should be used instead of /*+ FIRST_ROWS */.

Lets do a small experiment:

DROP TABLE t;

CREATE TABLE t AS SELECT ROWNUM id, ao.* FROM ALL_OBJECTS ao;

ANALYZE TABLE t
COMPUTE statistics;

SET timing on
SET autotrace traceonly
spool first_rows

prompt 'SELECT * FROM t;'
SELECT * FROM t;

prompt 'SELECT /*+ FIRST_ROWS */ * FROM t;'
SELECT /*+ FIRST_ROWS */ * FROM t;

prompt 'SELECT /*+ FIRST_ROWS_100 */ * FROM t;'
SELECT /*+ FIRST_ROWS_100 */ * FROM t;

prompt 'SELECT /*+ FIRST_ROWS(100) */ * FROM t;'
SELECT /*+ FIRST_ROWS(100) */ * FROM t;

prompt 'SELECT * FROM t WHERE ROWNUM <>
SELECT * FROM t WHERE ROWNUM <>

spool off
exit


And the results are:

CASE 1:
'SELECT * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.04
Cost = 286

CASE 2:
'SELECT /*+ FIRST_ROWS */ * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.03
Cost = 286

CASE 3:
'SELECT /*+ FIRST_ROWS_100 */ * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.02
Cost = 286

CASE 4:
'SELECT /*+ FIRST_ROWS(100) */ * FROM t;'
88430 rows selected.
Elapsed: 00:00:03.02
Cost = 4

CASE 5:
'SELECT * FROM t WHERE ROWNUM <>
100 rows selected.
Elapsed: 00:00:00.00
cost = 4

After looking at above results, it is clear that the best option is using ROWNUM <>