DDL: Triggers

SQL is a declarative language. That is to say that each statement is interpreted in isolation so that it is not possible to construct a flow of control as is possible in Java or C#. However, several database manufacturers provide proprietary extensions to standard SQL which do allow procedural control. The Oracle extension called PL/SQL allows the creation of a range of procedural code objects which can be stored in the database and accessed in a variety of ways. This can be useful, for example, to encapsulate the business logic of an application so that the same functionality can be delivered via several user interfaces without a lot of recoding.

For the most part, PL/SQL is beyond the scope of this module; however, there is one specific example that replicates the behaviour of the Microsoft synthetic key columns mentioned in the previous section. In SQL Server and MySQL, a new value is automatically generated for a column that is identified as a synthetic key. This is very convenient, and the Oracle alternative is quite cumbersome. The same behaviour can be produced, however, by creating a PL/SQL object called a trigger. Triggers - as the name suggests - respond to specified events such as the creation of a new row in a table. When the event occurs, the code in the trigger is executed in the context of the row involved in the event. The example below inserts the next value from the MATRIC_SEQ into the matric_no column of a new row in the STUDENT table.

    CREATE TRIGGER matric_trig
    BEFORE INSERT ON student
    FOR EACH ROW BEGIN
        SELECT matric_seq.NEXTVAL INTO :new.matric_no FROM dual;
    END;
    /

Some points of interest about the CREATE TRIGGER statement are:

:new is a special variable that refers to the row currently being created
dual is a dummy table provided in every Oracle database. It is used when the data being SELECTed does not actually come from a table. It could be used for example when displaying the current system date
The final forward slash is responsible for running the statement. The semicolon just marks the end of the PL/SQL code block

The syntax of PL/SQL is very different from standard SQL. If you are interested in knowing more, please consult the Oracle documentation. For the purposes of this module you will not need to know anything beyond the simple example above.

DDL: Triggers

Further reading