VI.7 Triggers

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

• make sure that a column is filled in with default information
• make sure that an audit row is inserted into another table
• after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Remember the mailing lists example from the beginning?

 

Suppose that you've been using the above data model to collect the names of Web site readers who'd like to be alerted when you add new articles. You haven't sent any notices for two months. You want to send everyone who signed up in the last two months a "Welcome to my Web service; thanks for signing up; here's what's new" message. You want to send the older subscribers a simple "here's what's new" message. But you can't do this because you didn't store a registration date. It is easy enough to fix the table:

alter table mailing_list add (registration_date date);

But what if you have 15 different Web scripts that use this table? The ones that query it aren't a problem. If they don't ask for the new column, they won't get it and won't realize that the table has been changed (this is one of the big selling features of the RDBMS). But the scripts that update the table will all need to be changed. If you miss a script, you're potentially stuck with a table where various random rows are missing critical information.

Oracle has a solution to your problem: triggers. A trigger is a way of telling Oracle "any time anyone touches this table, I want you to execute the following little fragment of code". Here's how we define the trigger mailing_list_registration_date:

create trigger mailing_list_registration_date
before insert on mailing_list
for each row
when (new.registration_date is null)
begin
 :new.registration_date := sysdate;
end;

Note that the trigger only runs when someone is trying to insert a row with a NULL registration date. If for some reason you need to copy over records from another database and they have a registration date, you don't want this trigger overwriting it with the date of the copy.

A second point to note about this trigger is that it runs for each row. This is called a "row-level trigger" rather than a "statement-level trigger", which runs once per transaction, and is usually not what you want.

A third point is that we're using the magic Oracle procedure sysdate, which will return the current time. The Oracle date type is precise to the second even though the default is to display only the day.

A fourth point is that, starting with Oracle 8, we could have done this more cleanly by adding a default sysdate instruction to the column's definition.

The final point worth noting is the :new. syntax. This lets you refer to the new values being inserted. There is an analogous :old. feature, which is useful for update triggers:

create or replace trigger mailing_list_update
before update on mailing_list
for each row
when (new.name <> old.name)
begin
  -- user is changing his or her name
  -- record the fact in an audit table
  insert into mailing_list_name_changes
    (old_name, new_name)
  values
    (:old.name, :new.name);
end;
/
show errors

This time we used the create or replace syntax. This keeps us from having to drop trigger mailing_list_update if we want to change the trigger definition. We added a comment using the SQL comment shortcut "--". The syntax new. and old. is used in the trigger definition, limiting the conditions under which the trigger runs. Between the begin and end, we're in a PL/SQL block. This is Oracle's procedural language, described later, in which new.name would mean "the name element from the record in new". So you have to use :new instead. It is obscurities like this that lead to competent Oracle consultants being paid $200+ per hour.

The "/" and show errors at the end are instructions to Oracle's SQL*Plus program. The slash says "I'm done typing this piece of PL/SQL, please evaluate what I've typed." The "show errors" says "if you found anything to object to in what I just typed, please tell me".

Also consider the general_comments table:

create table general_comments (
	comment_id		integer primary key,
	on_what_id		integer not null,
	on_which_table		varchar(50),
	user_id			not null references users,
	comment_date		date not null,
	ip_address		varchar(50) not null,
	modified_date	date not null,
	content			clob,
	-- is the content in HTML or plain text (the default)
	html_p			char(1) default 'f' check(html_p in ('t','f')),
	approved_p		char(1) default 't' check(approved_p in ('t','f'))
);

Users and administrators are both able to edit comments. We want to make sure that we know when a comment was last modified so that we can offer the administrator a "recently modified comments page". Rather than painstakingly go through all of our Web scripts that insert or update comments, we can specify an invariant in Oracle that "after every time someone touches the general_comments table, make sure that the modified_date column is set equal to the current date-time." Here's the trigger definition:

create trigger general_comments_modified
before insert or update on general_comments
for each row
begin
 :new.modified_date := sysdate;
end;
/
show errors

We're using the PL/SQL programming language, discussed in the procedural language chapter. In this case, it is a simple begin-end block that sets the :new value of modified_date to the result of calling the sysdate function.

When using SQL*Plus, you have to provide a / character to get the program to evaluate a trigger or PL/SQL function definition. You then have to say "show errors" if you want SQL*Plus to print out what went wrong. Unless you expect to write perfect code all the time, it can be convenient to leave these SQL*Plus incantations in your .sql files.

An Audit Table Example

  The canonical trigger example is the stuffing of an audit table. For example, in the data warehouse section of the ArsDigita Community System, we keep a table of user queries. Normally the SQL code for a query is kept in a query_columns table. However, sometimes a user might hand edit the generated SQL code, in which case we simply store that in the query_sqlqueries table. The SQL code for a query might be very important to a business and might have taken years to evolve. Even if we have good RDBMS backups, we don't want it getting erased because of a careless mouse click. So we add a queries_audit table to keep historical values of the query_sql column:

create table queries (
	query_id	integer primary key,
	query_name	varchar(100) not null,
	query_owner	not null references users,
	definition_time	date not null,
	-- if this is non-null, we just forget about all the query_columns
	-- stuff; the user has hand edited the SQL
	query_sql	varchar(4000)
);

create table queries_audit (
	query_id	integer not null,
	audit_time	date not null,
	query_sql	varchar(4000)
);

Note first that queries_audit has no primary key. If we were to make query_id the primary key, we'd only be able to store one history item per query, which is not our intent.

How to keep this table filled? We could do it by making sure that every Web script that might update the query_sql column inserts a row in queries_audit when appropriate. But how to enforce this after we've handed off our code to other programmers? Much better to let the RDBMS enforce the auditing:

create or replace trigger queries_audit_sql
before update on queries
for each row
when (old.query_sql is not null and (new.query_sql is null or old.query_sql <> new.query_sql))
begin
  insert into queries_audit (query_id, audit_time, query_sql)
  values
  (:old.query_id, sysdate, :old.query_sql);
end;

The structure of a row-level trigger is the following:

CREATE OR REPLACE TRIGGER ***trigger name***
***when*** ON ***which table***
FOR EACH ROW
***conditions for firing***
begin
  ***stuff to do***
end;

Let's go back and look at our trigger:

• It is named queries_audit_sql; this is really of no consequence so long as it doesn't conflict with the names of other triggers.
• It will be run before update, i.e., only when someone is executing an SQL UPDATE statement.
• It will be run only when someone is updating the table queries.
• It will be run only when the old value of query_sql is not null; we don't want to fill our audit table with NULLs.
• It will be run only when the new value of query_sql is different from the old value; we don't want to fill our audit table with rows because someone happens to be updating another column in queries. Note that SQL's three-valued logic forces us to put in an extra test for new.query_sql is null because old.query_sql <> new.query_sql will not evaluate to true when new.query_sql is NULL (a user wiping out the custom SQL altogether; a very important case to audit).

Creating More Elaborate Constraints with Triggers

The default Oracle mechanisms for constraining data are not always adequate. For example, the ArsDigita Community System auction module has a table called au_categories. The category_keyword column is a unique shorthand way of referring to a category in a URL. However, this column may be NULL because it is not the primary key to the table. The shorthand method of referring to the category is optional.

create table au_categories (
        category_id             integer primary key,
        -- shorthand for referring to this category,
        -- e.g. "bridges", for use in URLs
        category_keyword        varchar(30),
        -- human-readable name of this category,
        -- e.g. "All types of bridges"
        category_name           varchar(128) not null
);

We can't add a UNIQUE constraint to the category_keyword column. That would allow the table to only have one row where category_keyword was NULL. So we add a trigger that can execute an arbitrary PL/SQL expression and raise an error to prevent an INSERT if necessary:

create or replace trigger au_category_unique_tr
before insert
on au_categories
for each row
declare
        existing_count integer;
begin
        select count(*) into existing_count from au_categories
            where category_keyword = :new.category_keyword;
        if existing_count > 0
        then
            raise_application_error(-20010, 'Category keywords must be unique if used');
        end if;
end;

This trigger queries the table to find out if there are any matching keywords already inserted. If there are, it calls the built-in Oracle procedure raise_application_error to abort the transaction.
 

Reference

• Oracle Application Developer's Guide, Using Database Triggers

 

 

---

based on  SQL for Web Nerds