 Daffodil DB triggers -
07-08-2004
, 06:36 AM
Daffodil DB permits you to define procedures that are implicitly
executed when an INSERT, UPDATE, or DELETE statement is issued against
the associated table. These procedures are called database triggers.
Triggers can offer easy-to-implement solutions for the most
complicated database problems. Tracking unauthorized changes in a
database can be transformed from a lengthy and difficult security task
to an automated routine with the use of triggers. The secret is to
store data about each change as it occurs, so that you can determine
whose changing the data and when. The results of this tracking will
help you determine whether a data change is a simple mistake in
reporting, an unintentional mistake by an honest employee, or a real
security loophole that needs to be taken care of.
Knowing about the occurrence of an error is useless, unless you can
identify its source and prevent its recurrence. This article shows how
Daffodil DB triggers can serve as routine auditing procedures that
track errors whether they are due to human error or program logic. You
will learn how quickly triggers can help you track down errors or
simply create a historical record of normal processes
The execution of a trigger is transparent to the user. Triggers are
executed by the database when definite types of data manipulation
commands are executed on the tables. Specific columns can also be
updated with the use of triggers.
The main benefit of triggers is that they react automatically to a
specific type of modification made to a specific table. Keep the
following rules in mind when you are adding a trigger:
Only the table owner has permission to create triggers, and permission
cannot be transferred.
A trigger is considered a database object, so use object rules when
naming and referencing a trigger.
A trigger can reference a temporary table but cannot modify one.
A trigger cannot reference a system table.
Uses of Triggers
Triggers can supplement the usual capabilities of Daffodil DB to offer
a highly customized database. For example, a trigger can limit DML
operations against a table to those issued during a particular time. A
trigger could also restrict DML operations in particular cases. Other
uses for triggers are to
Automatically generate derived column values
Prevent invalid transactions
Enforce complex security authorizations
Enforce referential integrity across nodes in a distributed database
Impose complex business rules
Provide sophisticated auditing
Maintain synchronous table replicates
Collect statistics on table access
Types of Triggers
Daffodil DB supports Triggers of following types,
Row and Statement triggers
BEFORE and AFTER triggers
Row Triggers and Statement Triggers
When you define a trigger, you can specify the number of times the
trigger action is to be executed: once for every row affected by the
triggering statement or once for the triggering statement, no matter
how many rows it affects.
Row Triggers
A row trigger is fired every time the triggering statement affects the
table. For example, if an UPDATE statement updates several rows of a
table, a row trigger is fired one time for each row affected by the
UPDATE statement. If a triggering statement affects no rows, a row
trigger is not executed at all.
Syntax for creating a row level trigger in Daffodil DB can be
explained with the help of following example,
CREATE TRIGGER After Insert On test REFERENCING NEW As refert
FOR EACH ROW WHEN (testcol < 50 ) update test set testcol = 100
Statement Triggers
A statement trigger is fired once on behalf of the triggering
statement, regardless of the number of rows affected by the triggering
statement. For example, even if a DELETE statement deletes several
rows from a table, a statement-level DELETE trigger is fired only
once.
Statement triggers are useful if the code in the trigger action does
not depend on the data provided by the triggering statement or the
rows affected.
Following examples shows the syntax for creating a statement level
trigger in Daffodil DB
create trigger sample_trigger
before insert on teacher
for each statement
update teacher set salary=salary+20000
BEFORE and AFTER Triggers
When defining a trigger, you can specify the trigger timing, as
whether the trigger action is to be executed before or after the
triggering statement. BEFORE and AFTER apply to both statement and row
triggers.
Before Triggers
As the name suggests, before triggers are fired before the execution
of a triggering statement. For example a before trigger can be used to
take back up of data contained in a table before a particular batch of
statements.
Before triggers are combined with DML statements (Insert
/Update/Delete) to construct the triggering condition for a table. For
example, this query updates a table called audit before any
changes/insertions are made to the school table.
create trigger Trigger_Name
before insert on teacher
referencing old row as oldRow
for each row update audit set salary=salary+8000
BEFORE triggers execute the trigger action before the triggering
statement is executed. This type of trigger is commonly used in the
following situations:
When the trigger action should determine whether the triggering
statement should be allowed to execute. Users can choose BEFORE
triggers to eliminate unnecessary processing of the triggering
statement and its eventual rollback in cases where an exception is
raised in the trigger action.
To derive specific column values before completing a triggering INSERT
or UPDATE statement.
After Triggers
AFTER triggers execute the trigger action after the triggering
statement is executed. AFTER triggers are used in the following
situations:
When you want the triggering statement to complete before executing
the trigger action.
If a BEFORE trigger is already present, an AFTER trigger can perform
different actions on the same triggering statement.
The following examples depict the usage and syntax for triggers in
Daffodil DB
You can create a trigger on the Daffodil DB sample database table with
the help of following statement.
create trigger sample_trigger
before delete on student
referencing old row as oldRow
for each row update student set age=age+1
You can delete an existing trigger on the Daffodil DB sample database
table with the help of the following statement.
drop trigger sample_trigger
For more information and support regarding any issue related to
Daffodil DB Contact us at Daffodil DB Support .
Join other professional developers at Daffodil DB Online Community.
 |
Linear Mode
