Daffodil DB triggers

				
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: 


1. Only the table owner has permission to create triggers, and permission cannot be transferred. 


 
2. A trigger is considered a database object, so use object rules when naming and referencing a trigger. 


 
3. A trigger can reference a temporary table but cannot modify one. 


 
4. 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 a href="mail to:support@daffodildb.com">Daffodil DB Support . 


Join other professional developers at Daffodil DB Online Community.