|
   
9.
Triggers
 9.1
Trigger Components
9.2
Trigger Operation
9.3
Creating Triggers
9.4
Modifying a Trigger
9.5
Dropping a Trigger
9.6
Using Triggers
9.7
Enabling and Disabling Triggers
9.8
Privilege Required to Create Triggers
9. Triggers
Triggers
are a very useful and powerful feature of the DBMaker database server.
You can use triggers to automatically execute predefined commands in
response to specific events, regardless of which user or application
program generated those events. Triggers allow you to customize your
database in ways that may not be possible with standard SQL commands.
This allows the database to consistently control complex or unconventional
database operations without requiring any explicit action on the part
of users or application programs. You can use triggers to:
implement business rules.
create an audit trail of database activities.
derive additional values from existing data.
replicate data across multiple tables.
perform security authorization procedures.
control data integrity.
define unconventional integrity constraints.
You should
exercise restraint when using triggers to avoid forming complex interdependencies
within the database that may be difficult to follow and difficult to
change. In general, you should use triggers only when you cannot implement
the desired functionality using standard SQL commands and integrity
constraints.
9.1 Trigger Components
DBMaker
stores trigger definitions in the system catalog. Every DBMaker trigger
has six main components:
Trigger Name - a name that uniquely identifies the trigger.
Trigger Action Time - the time relative to the trigger even that
the trigger will fire.
Trigger Event - a specific situation that occurs in the database
in response to some user action, such as inserting data into a table.
Trigger Table - the name of the table the trigger executes on.
Trigger Action - an SQL statement or stored procedure that is executed
when the trigger event occurs.
Trigger Type - the type of trigger.
Each of
these components must be present in all triggers you create. In addition,
there is another optional component, the REFERENCING clause.
Trigger Name
The trigger
name uniquely identifies a trigger from other triggers associated with
the same table. Trigger names have a maximum length of 18 characters,
may contain letters, numbers, the underscore character, and the symbols
# and $. The first character cannot contain a number, and the name cannot
contain spaces.
Trigger Action Time
The trigger
action time specifies whether a trigger should fire before or after
the SQL statement that activates the trigger. You can use the BEFORE
and AFTER keywords to specify the trigger action time. The BEFORE keyword
indicates the trigger action will fire before the trigger statement,
and the AFTER keyword indicates the trigger action will fire after the
trigger statement. You can only specify one trigger time for each trigger.
Trigger Event
The trigger
event is the database operation that causes a trigger to operate, or
fire. The trigger event may be an INSERT, UPDATE, or DELETE statement
that operates on the trigger table. You can only specify one trigger
event for each trigger, but you can specify multiple trigger events
using multiple triggers.
Trigger Table
The trigger
table is the table the trigger event operates on. The trigger is associated
with this table. The trigger table must be a base table; you cannot
use a temporary table, view, or synonym as a trigger table. You can
only specify a single trigger table for each trigger.
Trigger Action
The trigger
action is the command a trigger executes when it fires. The trigger
action may be an INSERT, UPDATE, DELETE, or EXECUTE PROCEDURE statement.
You can only specify a single trigger action for each trigger.
Trigger Type
The trigger
type specifies how many times the trigger will fire for each trigger
event. There are two types of triggers: row triggers and statement triggers.
The FOR EACH ROW keywords specify a row trigger, which fires a trigger
action once for each row modified by the trigger event. The FOR EACH
STATEMENT keyword specify a statement trigger, which fires a trigger
action once and only once for each trigger event.
REFERENCING Clause
The REFERENCING
clause defines correlation names for the old and new values of a column.
This is primarily used when you cannot use the default OLD and NEW names
because of a conflict with a table with the same names.
9.2 Trigger Operation
Each time
a user or an application program causes a trigger event on a table,
DBMaker checks to see if a trigger should be fired, and executes triggers
if the triggering event has been defined. By firing triggers from within
the database, you can ensure that DBMaker handles data consistently
across all applications. This guarantees that when a specific event
occurs, a related action is also performed.
You can
use triggers to implement domain integrity, column integrity, referential
integrity, and unconventional integrity constraints. Though these can
also be done by declarative integrity control.
Triggers
do not have an owner, but are associated with a table instead.
Figure
9-1: Trigger event and action
9.3 Creating Triggers
The CREATE
TRIGGER command creates a new trigger associated with a specific table.
To execute this command, you must be the owner of the table associated
with the trigger or have DBA security privileges. You must also have
the necessary object privileges on all objects referenced in the trigger
definition to successfully create the trigger.
Basic Requirements
All CREATE
TRIGGER statements must contain at least the following:
a trigger name.
the trigger action time (before/after).
the trigger event.
the trigger table.
the trigger type (row/statement).
the trigger action.
Security Privileges
All SQL
statements in the trigger action operate with the same privileges as
the owner of the table associated with the trigger, and not with the
privileges of the user executing the trigger event. If the trigger exists,
any user can use it.
CREATE TRIGGER Syntax
The syntax
for the CREATE TRIGGER command is:
FOR
EACH ROW clause
FOR
EACH STATEMENT clause
Specifying the Trigger Name
The trigger
name uniquely identifies a trigger from other triggers associated with
the same table. Trigger names have a maximum length of 18 characters,
may contain letters, numbers, the underscore character, and the symbols
# and $. The first character cannot contain a number, and the name cannot
contain spaces.
Specifying the Trigger Action Time
The trigger
action time specifies whether a trigger should fire before or after
the SQL statement that activates the trigger. You can use the BEFORE
and AFTER keywords to specify the trigger action time. The BEFORE keyword
indicates the trigger action will fire before the trigger statement,
and the AFTER keyword indicates the trigger action will fire after the
trigger statement. You can only specify one trigger time for each trigger.
Specifying the Trigger Type
You can
use the trigger time and trigger type in combination to create four
triggers for each table for the same event. For each event the BEFORE/FOR
EACH ROW, AFTER/FOR EACH ROW, BEFORE/FOR EACH STATEMENT, and AFTER/FOR
EACH STATEMENT combinations are possible.
A BEFORE/FOR
EACH STATEMENT trigger executes once and only once before the triggering
statement is performed. That is before the occurrence of the trigger
event. An AFTER/FOR EACH STATEMENT trigger executes once and only once
after the triggering statement is complete. Note that BEFORE and AFTER
statement triggers are executed even if the triggering statement does
not process any rows.
For example,
you can define up to four triggers for an INSERT event on a single table:
BEFORE INSERT for each statement
BEFORE INSERT for each row
AFTER INSERT for each row
AFTER INSERT for each statement
|
You can
also define up to four triggers for a DELETE event on a single table:
BEFORE DELETE for each statement
BEFORE DELETE for each row
AFTER DELETE for each row
AFTER DELETE for each statement
|
The situation
is different for UPDATE events. You can create one UPDATE table trigger
which fires whenever the table is updated, or multiple UPDATE column
triggers which fire when specific columns are updated. UPDATE column
triggers may contain multiple columns, but columns in all UPDATE column
triggers in a table must be mutually exclusive. For example, you can
create a column trigger tr1 on table tb1 that has four
columns: c1, c2, c3, c4 now create a update
column trigger tr1 on table tb1:
CREATE TRIGGER tr1 AFTER UPDATE OF c1,c2 ON tb1
FOR EACH ROW
(INSERT INTO tb2 VALUES (old.c1, old.c2));
|
If you
try to create a second UPDATE column trigger tr2 that specifies
column c2, the command will fail because c2 already appears
in trigger tr1.
CREATE TRIGGER tr2 AFTER UPDATE OF c2,c3 ON tb1
FOR EACH ROW
(INSERT INTO tb3 VALUES (old.c2, old.c3));
|
So if there
are four columns in a table, you can create at most four UPDATE column
triggers or one UPDATE table trigger of the same type (for instance,
BEFORE/FOR EACH STATEMENT trigger).
The following
example is an UPDATE column trigger on table Sales. The totSales
field is a calculated field from other two fields unitPrice and
unitSale. Both unitPrice and unitSale are triggering
columns.
CREATE TRIGGER trTotalSale AFTER UPDATE OF unitPrice, unitSale ON Sales
FOR EACH ROW
(UPDATE Sales
SET totSales = new.unitPrice * new.unitSale);
|
Example
CREATE TRIGGER trig1 BEFORE UPDATE ON Orders
FOR EACH STATEMENT
(EXECUTE PROCEDURE checkPrivilege);
CREATE TRIGGER trig2 BEFORE UPDATE ON Orders
FOR EACH ROW
(INSERT INTO Log_Old_Value (old.customer,
old.amount));
CREATE TRIGGER trig3 AFTER UPDATE ON Orders
FOR EACH ROW
(INSERT INTO Log_New_Value (new.customer,
new.amount));
CREATE TRIGGER trig4 AFTER UPDATE ON Orders
FOR EACH STATEMENT
(EXECUTE PROCEDURE Log_Time);
|
In this
example we have four triggers. If you execute an UPDATE statement that
changes two rows of the Orders table, the effect and order of
the execution is as follows:
1. Procedure checkPrivilege is called.
2. Insert one row to Log_Old_Value table.
3. Update one row.
4. Insert one row to Log_New_Value table.
5. Procedure Log_Time is called.
6. Insert one row to Log_Old_Value table.
7. Update one row.
8. Insert one row to Log_New_Value table.
9. Procedure Log_Time is called.
|
Specifying the Triggered Action
The triggered
action is the SQL statement that is performed when the trigger event
occurs. The triggered action can be an INSERT, DELETE, UPDATE, or EXECUTE
PROCEDURE statement. No other statements are allowed. Stored procedures
cannot contain COMMIT, ROLLBACK or SAVEPOINT transaction control statements.
Triggers can specify only a single triggered action, which must be enclosed
in parentheses.
For example,
the following statement creates a trigger on table emp.
CREATE TRIGGER trigExample AFTER INSERT ON emp
FOR EACH ROW WHEN (new.empNo > 0)
(INSERT INTO personnel(new.empName,
new.empAddress, new.Manager));
|
In this
example, the trigger name is trigExample. The AFTER option is
specified, which means this trigger will be fired after the INSERT statement
executes on table emp. The triggering event is INSERT, the triggering
table is emp. The trigger type is FOR EACH ROW. The SQL action
that is triggered is INSERT.
CREATE TRIGGER trDelAcct AFTER DELETE ON Account
FOR EACH ROW
(INSERT INTO oldAccount
VALUES (Old.Customer_name));
|
In the
above example, the trigger trDelAcct will add the deleted customer
name into the oldAccount table when one deletes a record from
the Account table. You cannot create a trigger on a temporary
table, view, or system table.
Using the Referencing Clause
When you
create a row trigger, you usually need to indicate in the action body
whether you are referring to the value of a column before or after the
triggering statement fires. For example, if you want to log the old
price and new price when you update the price of a sale item, you can
use the keywords OLD and NEW as shown in the first two examples in this
chapter.
However
some rare cases you may have tables named NEW or OLD. If this is the
case, you can use the referencing clause to define correlation names.
The reference clause lets you create two prefixes you can use with a
column name, one to reference the old value of the column and one to
reference the new value. These prefixes are called correlation names.
You use the keyword OLD and NEW to indicate the correlation names.
CREATE TRIGGER tr_log_price AFTER UPDATE OF price ON New
REFERENCING OLD as pre NEW as post
FOR EACH ROW
(INSERT INTO logTbl
VALUES (item_no, today(),
pre.price, post.price));
|
In this
example, the triggering table name is NEW, so we define the correlation
name pre and post and used them in the action body. Referencing
clauses are only valid for row triggers, and are not allowed in statement
triggers.
If a trigger
event is INSERT, there is no old value for the newly inserted record,
so the old value is not available. Similarly, if the trigger event is
DELETE, there is no new value for the deleted record, so the new value
is not available. For an UPDATE event trigger, both old and new values
are available.
Using the WHEN Condition
You may
optionally precede a FOR EACH ROW triggered action with a WHEN condition
clause to make the action execution dependent on the result of a boolean
expression. The WHEN clause consists of a keyword WHEN followed by the
conditional statement in parentheses. The WHEN clause follows the action
time and precedes the triggered action body. The WHEN clause is not
allowed in the definition of a statement trigger, it is only allowed
in row trigger.
The following
trigger will log a customer complaint into the logComplain table
when a customer calls to complain about something. (Assume the call
code 'c' means it's a complaint call.)
CREATE TRIGGER tr_log_complain INSERT ON Customer_Call
FOR EACH ROW
WHEN (new.call_code = 'c')
(INSERT INTO logComplain
VALUES (Today(), Cus_Name));
|
If the
WHEN condition is included in a trigger definition, the WHEN clause
is evaluated for each row. If the WHEN condition evaluates to TRUE for
a row, the triggered action is fired for that row. If the WHEN condition
evaluates to FALSE or unknown for a row, the triggered action is not
fired for that row.
The result
of WHEN condition only affects the execution of the triggered action,
it has no effect on the triggering statement.
Example
To monitor
the modification on table emp, we can create three triggers to
record all INSERT, UPDATE and DELETE operations on table emp.
CREATE TRIGGER trig_emp_insert AFTER INSERT ON emp
FOR EACH ROW
(INSERT INTO emp_audit
VALUES (NULL, NULL,
new.empId, new.empName));
CREATE TRIGGER trig_emp_update AFTER UPDATE ON emp
FOR EACH ROW
(INSERT INTO emp_audit
VALUES (old.empId, old.empName,
new.empId, new.empName));
CREATE TRIGGER trig_emp_update AFTER DELETE ON emp
FOR EACH ROW
(INSERT INTO emp_audit
VALUES (old.empId, old.empName,
NULL, NULL));
|
Example
If a primary
key is changed, all the foreign keys can be changed in cascade. Suppose
deptNo is the primary key on table dept, DeptNo is foreign
key on table emp.
CREATE TRIGGER trig_upd_dept BEFORE UPDATE OF deptNo ON dept
FOR EACH ROW
WHEN (NEW.deptNo <> OLD.deptNo)
(UPDATE emp SET emp.DeptNo = NEW.deptNo
WHERE emp.DeptNo = OLD.deptNo);
|
Example
If the
primary key is deleted, all the foreign keys can be deleted in cascade.
CREATE TRIGGER trig_del_dept BEFORE DELETE ON dept
FOR EACH ROW
(DELETE FROM emp
WHERE emp.DeptNo = OLD.deptNo);
|
Example
If a primary
key is updated, all the foreign keys can be set to NULL.
CREATE TRIGGER trig_del_dept BEFORE UPDATE ON dept
FOR EACH ROW
(UPDATE emp set DeptNo = NULL
WHERE emp.DeptNo = OLD.deptNo);
|
Example
If the
parts on hand is lower than a level, then you can specify the parts
should be reordered. The part number and quantity will be recorded to
a table called 'pending_orders' for further action.
Inventory:
part_no int, parts_on_hand int, reorder_level int, reorder_qty int
pending_orders:
part_no int, qty int, order_date date
CREATE TRIGGER tr_reorder AFTER UPDATE OF parts_on_hand ON Inventory
FOR EACH ROW
WHEN (new.parts_on_hand < new.reorder_level)
(INSERT INTO pending_orders
VALUES (new.part_no,
new.reorder_qty, today()))
|
9.4 Modifying a Trigger
A trigger
can not be explicitly modified, but its definition can be replaced.
When you want to use modify the trigger definition, use the ALTER TRIGGER
COMMAND.
ALTER TRIGGER Syntax
FOR
EACH ROW clause
FOR
EACH STATEMENT clause
Replacing the Triggered Action
To replace
the triggered action, you should use a command similar to the following:
ALTER TRIGGER tr1 REPLACE WITH ...
|
For example,
when an employee quits, if the employee is a manager, his/her data need
to be deleted from the manager table, so a trigger is created on table
employee as follows:
CREATE TRIGGER delEmp AFTER DELETE ON employee
FOR EACH ROW
( DELETE FROM manager WHERE empId = old.empId )
|
If you
want to add another condition in the triggered action, such as, delete
the data from table manager table only when the employee is a project
manager, you can use the following command to modify the trigger:
ALTER TRIGGER delEmp REPLACE WITH AFTER DELETE ON employee
FOR EACH ROW
( DELETE FROM manager
WHERE empId = old.empId
AND title = 'Project Mananger')
|
Alternatively,
the trigger can be dropped and recreated.
9.5 Dropping a Trigger
To delete
a trigger from the database, use the DROP TRIGGER statement
DROP TRIGGER Syntax
Dropping the Trigger
To drop
a trigger, specify the name of the trigger you wish to delete, and the
table the trigger is associated with:
DROP TRIGGER myTrigger FROM myTable;
|
Delete
a table will cause triggers that reference this table in either event
or action to be deleted. When a table schema is altered, next time the
trigger is executed, DBMaker will try to execute the trigger according
to the new table definition, however, if the specified column in a triggering
event or action is dropped, the trigger execution will fail and the
triggering statement will fail, too. In this situation, users need to
drop the trigger or modify the trigger definition according to the new
table schema.
For example,
a trigger tr1 is created on table t1:
CREATE TRIGGER tr1 AFTER UPDATE ON t1
FOR EACH ROW
(DELETE FROM t2 WHERE c1 = old.c1)
|
If the
column c1 in table t2 is dropped or its type is changed. When
the triggering statement (update on t1) is performed, which cause
DBMS tries to fire trigger tr1, execution error will happen.
9.6 Using Triggers
There are
several different ways you can use triggers. This section provides an
introduction to some of them.
Using Stored Procedures in Action Body
Perhaps
the most powerful feature of trigger is the ability to call a stored
procedure as a trigger action. The EXECUTE PROCEDURE statement, which
calls a stored procedure, enables you to pass data from the triggering
table to the stored procedure and execute the procedure.
CREATE TRIGGER trLogPrice AFTER UPDATE OF price ON Sales
FOR EACH ROW
(EXECUTE PROCEDURE
logPrice(item_no,
new.price,
old.price));
|
Users can
pass values to a stored procedure in the argument list. If the stored
procedure call is part of the action of a row trigger, users can use
the OLD and NEW correlation values to pass the column values to the
stored procedure. If the stored procedure is part of action of a statement
trigger, users can only pass constants to the stored procedure.
Within
a trigger action, you can update non-triggering columns in the triggering
table. You can do this with or without stored procedure. Note that the
stored procedure fired by a trigger cannot contain transaction control
statements, like BEGIN WORK, COMMIT WORK, ROLLBACK WORK, SAVEPOINT,
or DDL statements.
The stored
procedure as a trigger action can not be a cursory procedure which returns
more than one row.
Trigger Execution Order
If a table
has multiple update column triggers and when an update operation triggers
more than one trigger, which trigger action will be executed first?
Usually the order of trigger execution is not a concern for the trigger
designer. But if the order matters then you need to know the rule of
trigger execution order.
The order
of trigger execution is determined by the column numbers of the triggering
columns of the triggers. The trigger execution begins with the trigger
which has smallest triggering column number in its triggering column
list and proceeds in order to the trigger which has larger triggering
column number.
The following
example assumes table t1 has four columns named a, b,
c, and d.
CREATE TRIGGER trig1 AFTER UPDATE OF a,c ON t1
FOR EACH STATEMENT (UPDATE t2 set c1=c1+1)
CREATE TRIGGER trig2 AFTER UPDATE OF b,d ON t1
FOR EACH STATEMENT (UPDATE t2 set c2=c2+1)
|
The operation
UPDATE t1 SET b=b+1, c=c+1 will trigger both triggers. Trigger trig1
with triggering columns a, c will be executed before trigger
trig2 with triggering columns b, d because trig1
has smaller triggering column than trig2.
Security and Triggers
First you
must have permission to run the triggering event, otherwise you won't
trigger the event. But you don't have to have the permission to run
the triggered action because the SQL statements in the triggered action
operate under the privilege domain of the trigger owner, instead of
the privilege domain of the user executing the triggering statement.
That is,
once the trigger is created successfully since the trigger creator has
privilege to execute the triggered action. Any one else who can issue
the triggering statement can fire the trigger.
For example,
if user B can update on both table T1 and T2, user
A can update T1, but not T2. Now user B
create a trigger on update T1, and the action is update T2.
When user A update T1, the triggered action (update T2)
is executed successfully since the triggered action is running under
the privilege domain of user B. This security rule is simpler
and more reasonable, compared to requesting any user who execute the
triggering statement has the privilege to execute the triggered action.
Cursors and Triggers
UPDATE
or DELETE statements within a cursor act differently than a singleton
update or delete statement. The entire trigger will be executed with
each update or delete with the WHERE CURRENT OF clause. For example,
if four rows are changed with a cursor, the BEFORE/FOR EACH STATEMENT,
BEFORE/FOR EACH ROW, AFTER/FOR EACH STATEMENT and AFTER/FOR EACH ROW
triggers will be executed four times, once for each row.
Cascading Triggers
Executing
one trigger may cause another trigger to be executed. Sometimes you
can use cascading triggers to enforce referential integrity. The maximum
number of triggers in a cascading sequence is 64 in DBMaker. In the
following example, when you delete a customer from the customer table,
it will trigger the action to delete this customer related records in
the order table, which in turn will trigger the action to delete order
related records in the item table.
CREATE TRIGGER cas1 AFTER DELETE ON customer
FOR EACH ROW
(DELETE FROM orders WHERE cust_num = old.cust_num);
CREATE TRIGGER cas2 AFTER DELETE ON orders
FOR EACH ROW
(DELETE FROM items WHERE order_num = old.order_num);
|
In DBMaker,
if users create recursive triggers, we won't return error at trigger
creation time. However, users will get error when the recursive triggers
execute and meet the maximum limit of cascading trigger level.
9.7 Enabling and Disabling Triggers
When a
trigger is created, the trigger is in "enabled" mode, which
means the triggered action executes when event occurs. However, sometimes
users may need to disable a trigger for some reasons, such as:
when users have to load a large amount of data, disable the triggers
temporarily will make the loading operation quicker.
when the objects referenced in a trigger is unavailable.
For example,
to disable trigger tr1 on table t1, you can type this:
ALTER TRIGGER tr1 ON t1 DISABLE
|
The command
to enable trigger tr1 on table t1 is:
ALTER TRIGGER tr1 ON t1 ENABLE
|
In summary,
a trigger has two possible modes:
Enabled - A trigger is enabled when created. The triggered action
executes when event occurs.
Disabled - A disabled trigger does not execute its action, even
if the event occurs.
9.8 Privilege Required to Create Triggers
To create
a trigger on a table, user must be the table owner or DBA. When creating
a trigger, the privileges to all the objects referenced in the trigger
definition must be granted to the trigger creator such that the trigger
creator can create it successfully.
In DBMaker,
a trigger has no owner, it is associated to the table. Table owner or
DBA has all the privileges on the triggers associated to it. They can
create, drop or alter the triggers.
The SQL
statements in the triggered action operate under the privilege domain
of the trigger owner, instead of the privilege domain of the user executing
the triggering statement.
|
