13.7 How do I…Rebuild CREATE TRIGGER statements?

Problem

I need to re-create the statements used to create some of the triggers in the database. I either lost the original files or entered the trigger directly into SQL*Plus without saving the CREATE TRIGGER statements. I know that throughout the book we have used the data dictionary and SQL*Plus to rebuild statements. How do I rebuild CREATE TRIGGER statements?

Technique

There are data dictionary views containing information about triggers, which are USER_TRIGGERS, ALL_TRIGGERS, and DBA_TRIGGERS. When you rebuild the CREATE TRIGGER statement for a trigger, you can connect to the database as the owner of the trigger and use the USER_TRIGGERS or ALL_TRIGGERS data dictionary view. The DBA_TRIGGERS view contains all views in the database and requires privileges in the DBA role. The description of the USER_TRIGGERS view is shown in Figure 13.14. The TRIGGER_BODY column contains the text of the trigger. The datatype of the column is LONG, so it will be slightly harder to work with. The TRIGGER_NAME, TRIGGER_TYPE, and TRIGGERING_EVENT columns will be concatenated with the trigger body in order to rebuild the statement.

Steps

1. Run SQL*Plus and connect as the WAITE user account. If you do not have the DEPT13 table and the triggers created on it in your schema, then run CHP13_20.SQL from Step 1 of How-To 13.5. The DEPT13 table is created along with the two triggers, DEPT_BI_13 and DEPT_AI_13. The USER_TRIGGERS provides valuable information about triggers as examined in the previous How-To. In this How-To, the same view is used to rebuild the code that originally created the trigger.

2. Run the CHP13_26.SQL in SQL*Plus, as shown in Figure 13.26. The file contains a query of the USER_TRIGGERS data dictionary view displaying the header and body of the trigger.

The DESCRIPTION column in line 2 returns the trigger header. The TRIGGER_BODY column in line 3 returns the trigger body. Line 5 specifies the USER_TRIGGERS view as the source of the data and line 7 specifies the name of the trigger.

Because the TRIGGER_BODY column is of the LONG datatype, you may increase the value of the LONG SQL*Plus variable to 2000, if you have SQL*Plus complaints about the data being too long to display. Use the SET LONG 2000 command at the prompt and try running the query again. If a lot of columns are returned by a query, the ARRAYSIZE system variable can be decreased before running the query. For example, use the SET ARRAYSIZE 1 command at the prompt to reduce the value of SQL*Plus ARRAYSIZE variable.

LINESIZE is another SQL*Plus variable that you may occasionally need to adjust to fit the output of a column to a single line. For example, use the SET LINESIZE 132 command to set the SQL*Plus LINESIZE variable to 132.

With the DESCRIPTION and TRIGGER_BODY columns, the result of the query almost looks like a CREATE TRIGGER statement, except for the CREATE OR REPLACE TRIGGER keywords. The next step presents the query to actually rebuild the trigger code.

3. Load CHP13_27.SQL into the SQL buffer. The file contains a query that returns the actual trigger code to re-create the trigger. Figure 13.27 displays the results of the operation.

The HEADING SQL*Plus variable is set to OFF to suppress the column headings. Line 1 prefixes the CREATE OR REPLACE TRIGGER keywords and concatenates the trigger header to it. Line 3 retrieves the trigger body. When this query is executed, it returns the exact code that originally created the trigger. You can spool the output to a file using the SPOOL command and then run the spooled file to re-create the trigger.

With this query, you can only re-create triggers that belong to the current user account. A query to ALL_TRIGGERS or DBA_TRIGGERS enables you to create re-create all triggers in the system, provided you have DBA permissions.

How It Works

Throughout the book, we use dictionary views to create DDL statements. In this case, a CREATE TRIGGER statement is rebuilt. The ALL_TRIGGERS data dictionary view contains information about the triggers in the database.

Step 1 creates the sample table and trigger used throughout this How-To. Step 2 displays the DESCRIPTION and TRIGGER_BODY columns, returning the header and code section of the trigger. Step 2 also creates the trigger specification by prefixing the CREATE OR REPLACE TRIGGER keywords to the DESCRIPTION column value. The process is completed by concatenating the TRIGGER_BODY column value. The resulting output is the trigger code that originally created the trigger.

Comments

The process presented in this How-To can be used to rebuild CREATE TRIGGER statements when the original source code is no longer available. Rebuilding CREATE TRIGGER statements can be a necessary database management task. If you need to create all the triggers on a table or in the database, a simple modification to the WHERE clause of the query is all you need.