Triggers and Indexes

SQL Triggers are SQL Statements or a set SQL of statements which is stored to be activated or fired when an event associating with a database table , view or schema occurs. The event can be INSERT, UPDATE and DELETE.

SQL Triggers provides an alternative way to check integrity and to run scheduled tasks. However, SQL Triggers executes invisibly from the client application which connects to the database server so it is difficult to figure out what happened in the underlying  database layer.

Since it run on every update made to the table, it adds workload to the database and cause system runs slower.

 

Structure of the trigger(PL/SQL)

CREATE [OR REPLACE]
TRIGGER BEFORE (or AFTER)
INSERT (or UPDATE [OF COLUMNS] or DELETE)
on [FOR EACH ROW [WHEN]]
BEGIN
...
END; 

 

Indexes allow locating information within a database fast, much like the purpose they serve in the books or in a telephone directory. An index can be created on single column of a table or a combination of columns in a table of a database. 

A table index has pointers to the values stored in specified column or combination of columns of the table. These pointers are ordered depending on the sort order specified in the index

 

CREATE [UNIQUE] INDEX
on (,)

 

**UNIQUE : duplicate values in the index are not allowed