Trigger an Overview

Trigger is a special stored procedure that runs automatically during or after data modifications. Commands that will activate the trigger are UPDATE, INSERT, and DELETE. Trigger is used to maintain data integrity and implement complex business rules. Trigger made using Transtact-SQL language or SQL Enterprise Manager. Trigger management tasks include changing, rename, view, delete, and create a trigger inactive.

Trigger uses two virtual tables that are Inserted and Deleted to detect data modification. To program the trigger, you must understand that tables and also Transtact-SQL language.
To maintain data integrity, you should be able to use some alternative, such as triggers or constraints. You can also use the primary key and unique key to identify rows in a table uniquely. You can also use the default values and domains in the limit of allowed values in a column. Referential integrity is used to ensure the validity of relationships between tables. We recommend that you use these constraints before choosing a trigger, because trigger can perform more complicated process, but the heavier the system load.

Use a trigger for the following cases :

• If the use of declarative way cannot meet the necessary requirements.
• A change must alter the other tables are linked.
• If the database requires normalizes and automatically convert existing redundant data in multiple tables.
• If a value in a table must be validated with data on other tables.
• If a value in a table must be validated with data on other tables.

Trigger capabilities :

• Trigger to maintain referential integrity by making changes in or deletion of a foreign key data in the database.
  
• Trigger can work on multiple columns in a database even in objects outside the database. You can also use a trigger on the view.
• A trigger can perform some action and trigger can be activated by some event.

In addition to these capabilities, triggers, also has a limitation. Trigger cannot be created on a system or temporary table, although the command in the trigger table refers to the system or temporary table. Trigger cannot be activated manually, but automatically activated in case of events UPDATE, INSERT, and DELETE. The trigger always contains one or more event before Transtact-SQL command.

When INSERT or UPDATE that activated trigger, trigger will store new data, or data modification results in a table called Inserted. If DELETE command that activated trigger, trigger will store new data, or data modification results in a table called Deleted. Table is located in memory and is read by the trigger with the command-SQL Transtact.

Single Transtact SQL Command

Transtact SQL command is a command non procedural, meaning that the command does not declare the stretcher on the steps executed by the server. Server must analyze command to obtain the most efficient. This process is called optimization and is performed by the query optimizer.

A command, will have several execution plans. Of the possibilities would serve targeted selected a best execution plan, this process is called optimization. Query optimizer is the most important components in a system of the database SQL, because it can handle different conditions without having to change the program.

For example to process a SELECT command, the SQL Server run the following commands:

1. SQL trace and split into logical units such as keywords, expressions, operators, and the identifier.
2. Formed query tree to illustrate the logical steps necessary to change the data source into the desired shape.
3. Query optimizer to analyze all the ways to access the source tables and choose the steps that use the fastest and least resources. Query trees, repaired to record the steps that ultimately yield better execution plans.
4. Database engine started to run the execution plan.

Basic steps for processing the SELECT command can be used for other commands such as INSERT, UPDATE, and the DELETE. UDPATE and DELETE commands that have a target on the set of rows are basically doing the same process with the SELECT command. After the lines obtained were alteration or deletion.

Transtact-SQL Overview

Transtact-SQL is the development of the SQL language published by the International Organization for Standardization (ISO) and American National Standards Institute (ANSI). Transtact-SQL, is needed in the SQL server. Every application that communicates with SQL Server will send Transtact-SQL command to the server.

Transtact-SQL command is a set of programs that run on ata data objects in the database. SQL Server has three types of Transtact-SQL, the Data Definition Language, Data Control Language, and Manipulation Language.

Data Definition Language (DDL)

Data Definition Language (DDL) is part of a database management system, used to define the data and manage all the attributes and properties from a database. With the DDL you can specify the column definitions, key columns, files location, and storage strategy.

General form of DDL statements:
CREATE object_name
ALTER object_name
DROP object_name

Data Control Language (DCL)

DCL is used to adjust the controls on database objects. Commands can be used is the GRANT and REVOKE.

GRANT command is used to give rights to user to access a database. REVOKE command is used to dispose of the rights granted to the user

Manipulation Language (DML)

DML is used to display, add, modify, and delete data in the objects defined by DDL. DDL Commands are SELECT, INSERT, UPDATE, DELETE.

SELECT command will search the records from the database
INSERT command is used to add new records on a table or view.
UPDATE command is used to change the data in a table.
DELETE command is used to remove data in a table.