UP1 :: บันทึกเรื่องราวต่างๆ

เพิ่งเคยเขียน  Trigger บน  Oracle ครับเลยเอามาฝาก

Triggers

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Below is the syntax for creating a trigger in Oracle (this syntax has been simplified; for the complete version try HELP CREATE TRIGGER in sqlplus):

CREATE [OR REPLACE] TRIGGER <trigger_name>
    {BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
    [FOR EACH ROW [WHEN (<trigger_condition>)]]
    <trigger_body>

Some important points to note:

• You can create only BEFORE and AFTER triggers for tables. (INSTEAD OF triggers are only available for views; typically they are used to implement view updates.)

• You may specify up to three triggering events using the keyword OR. Furthermore, UPDATE can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some examples:

      ... INSERT ON R ...
      ... INSERT OR DELETE OR UPDATE ON R ...
      ... UPDATE OF A, B OR INSERT ON R ...

• If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.

• For a row-level trigger, a trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, a trigger is fired by every triggering event.

• <trigger_body> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop. The restrictions on <trigger_body> include:
  • You cannot modify the same relation whose modification is the event triggering the trigger.
  • You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constraint.

We illustrate Oracle's syntax for creating a trigger through an example based on the following two tables:

CREATE TABLE T4 (a INTEGER, b CHAR(10));
CREATE TABLE T5 (c CHAR(10), d INTEGER);

We create a trigger that may insert a tuple into T5 when a tuple is inserted into T4. Specifically, the trigger checks whether the new tuple has a first component 10 or less, and if so inserts the reverse tuple into T5:

CREATE TRIGGER trig1
    AFTER INSERT ON T4
    FOR EACH ROW
    WHEN (NEW.a <= 10)
    BEGIN
        INSERT INTO T5 VALUES(:NEW.b, :NEW.a);
    END trig1;
.
run;

The special variables NEW and OLD are available to refer to new and old tuples respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon!

Again, notice that we end the CREATE TRIGGER statement with a dot and run, as for all PL/SQL statements in general. Running the CREATE TRIGGER statement only creates the trigger; it does not execute the trigger. Only a triggering event, such as an insertion into T4 in this example, causes the trigger to execute.

To view information about your triggers, use the following:

select trigger_name from user_triggers;

select trigger_type, table_name, triggering_event
from user_triggers
where trigger_name = '<trigger_name>';

To drop a trigger:

drop trigger <trigger_name>;

To disable or enable a trigger:

alter trigger <trigger_name> {disable|enable};