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.