Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

SQL Triggers

Most relational database servers provide a mechanism called triggers, for enforcing integrity constraints. Triggers are compiled once and are stored on the database server. Triggers are invoked when a particular action occurs on a particular table. Usually the action performed by a trigger affects a different table, otherwise the action becomes recursive (with the possibility of repeating forever, thus some relational databases do not allow trigger actions ion the same table as the one that invoked them). Triggers can only apply to one table or view, but multiple triggers can be created on the same table or view.

How Triggers work

Every trigger definition must contain a name, the action to be performed and the execution. Thus the general definition of a trigger looks as follows:

CREATE TRIGGER trigger_name ON table_name
 [BEFORE | AFTER] [INSERT | UPDATE | DELETE ]
 AS execution_code

In general terms a trigger is invoked when one of the actions is performed on a table:

Data is inserted

Data is updated

Data is deleted

Here is what happens, when you define a trigger, you specify when the trigger on the table should be invoked. You either invoke the trigger before or after the action is performed on the table. For example, we could put a trigger on a table so that it can log an audit trail, to record what the values were after the update. To do this we could define a trigger like this:

 (using MSSQL syntax)

create trigger update_audit on employee 

after update AS if update(dateofbirth)

begin

   insert into audit(changed_field, changes) 

   select 'dateofbirth', dateofbirth from deleted;

end;

Code Analysis

This code defines a trigger to update the audit table when an update is made on the date of birth field of the employee table. Once an update is made on employee table, the trigger inserts the old value into the audit table. Note how the code is referring to the table deleted. That table does not actually exist in your database structure, it gets created when an update is made just to keep the old values long enough to be stored in the permanent table, which in this case is the audit table. After that, the database system destroys it.

Creating and dropping SQL Views
In SQL, a view is like a virtual table. It be haves like a table except it does not store any data. Meaningful use of a view is usually to gather and simplify presentation of data from multiple tables. A view is compiled once and is stored on the database server and can be used wh

Creating database tables with SQL
To create a table in SQL, you use a command that requires the name of the table as well as names and data types of the fields of that table. In general, the syntax for creating a table is as follows: CREATE table_name ( column_name data_type); The table nam

Selecting data from multiple tables
So far we have covered methods of retrieving data from single table. In real life however, you hardly get to only retrieve data from one table. In other words, to retrieve and interpret information, you normally get such information from different tables each of which will contain only information a

Udjuni.com

SQL Triggers

How Triggers work

Code Analysis

RELATED ARTICLES: