Database Reference
In-Depth Information
Indexes provide fast access to data. Indexes improve the performance of retrieval oper-
ations of indexes. Another use of Indexes are data filtering and data sorting. Multiple
columns can be defined in an index A SQL index is created on a column or a group of
columns within a table. The syntax to create an index.
CREATE [UNIQUE] INDEX indexname
ON tablename (columnname [ASC | DESC] [,…n]);
Dropping an Index
Index can be deleted at any time. No data will be lost from the table, and the
index can be recreated at any time.
The syntax to create an index.
DROP INDEX tablename.indexname;
Example: create an index on the Customer_name column from the Customer table. Sol:
CREATE INDEX idx_LastName
ON Customer (Customer_name);
Example: Remove an index on the Customer_name column of the Customer table.
Sol:
DROP INDEX Customer .Customer_name;
Triggers
A trigger is very similar to a stored procedure. a trigger is executed automatically in re-
sponse to a specific event. A trigger can be defined to automatically “fire” whenever an
INSERT, an UPDATE, or a DELETE command is issued on a particular table. We can do
following things with triggers
 Write to an audit log when rows are changed.  Synchronize changes to a backup data-
base.  Cascade changes and maintain referential integrity.  Enforce complex data valida-
tion and business rules.
Example: create a trigger that converts the Customer_state column in the Customer table to
uppercase on all INSERT and UPDATE operations.
Sol:
CREATE TRIGGER customer_st
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customer
Search WWH ::




Custom Search