What is a Database Trigger?

Link:

http://www.essentialsql.com/what-is-a-database-trigger/

Copy ...

What is a Database Trigger?

A database trigger is special stored procedure that's run when specific actions occur within a database.  Most triggers is defined to run when changes is made to a table ' s data. Triggers can defined to run instead of or after DML (Data manipulation Language) actions such as Inse RT, UPDATE, and DELETE.

Triggers Help the Database Designer ensure certain actions, such as maintaining an audit file, is completed regardless of Which program or user makes changes to the data.

The programs is called triggers since an event, such as adding a record to a table, fires their execution.

Triggers and their implementations is specific to database vendors. In this article we ll focus on Microsoft SQL server; However, the concepts is the same or similar in Oracle and MySQL.

Note:all The examples for this lesson is based on Microsoft SQL Server Management Studio and the AdventureWorks2012 data  Base. You can get the started using these free tools using the My guide Getting started using SQL Server.

Events

The triggers can occur after or INSTEAD of a DML action.  Triggers is associated with the database DML actions INSERT, UPDATE, and DELETE. Triggers is defined to run when these actions is executed on a specific table.

After triggers

Once the DML actions, such as an INSERT completes, the after trigger executes. Here is some key characteristics of after triggers:

• After triggers is run after a DML action, such as an INSERT statement and any ensuing referential cascade actions and con Straint checks has run.
• You can ' t cancel the database action using a after trigger. This was because the action has already completed.
• One or more after triggers per action can is defined on a table, but to keep things simple I recommend only defining one.
• You can ' t define after triggers on.

INSTEAD of Triggers

INSTEAD of triggers, as their name implies, run in place of the DML action which caused them to fire. Items to consider when using INSTEAD of triggers include:

• An INSTEAD of trigger overrides the triggering action. If an INSTEAD of trigger are defined to execute on a insert statement, then once the INSERT statement attempt to run, cont Rol is immediately passed to the INSTEAD of trigger.
• At the most, one INSTEAD of trigger can is defined per action for a table. This makes sense, as if you had to "INSTEAD of" triggers for an insert, which one should run?

Special Database Objects

Triggers use of Special database objects, INSERTED and DELETED, to access rows affected by the database actions. Within the scope of a trigger the INSERTED and DELETE objects has the same columns as the trigger ' s table.

The INSERTED table contains all the new values;  Whereas, the DELETED table contains old values. Here's how the tables is used:

• Insert–use the INSERTED table to determine which rows were added to the table.
• Delete–use the DELETED table to see which rows were removed from the table.
• Update–use the INSERTED table to inspect the new or updated values and the DELETED table to see the values prior to UPDA Te.

Definition

A trigger is defined for a specific table and one or more events. In the most database management systems you can only define one trigger per table.

Below is a example trigger from the ADVENTUREWORKS2012 database.

You'll notice the syntax for a trigger are very similar to that of a stored procedure.  In fact, the trigger uses the same language to implement it logic as do stored procedures. In MS sql, the is T-SQL; Whereas in Oracle it is PL/SQL.

Here is some important parts to a trigger:

1. The CREATE statement–it defines which table is associated with the trigger. In addition this statement was used to specify when the trigger executes (e.g. after insert).
2. The actual program. In the example, this program runs whenever one or more rows is inserted into the WorkOrder table.
3. Special database objects–triggers use specially defined databases objects such as INSERTED, or DELETED to access records Affected by the database action.
4. The example the trigger is using the INSERTED object to gain access to the newly created rows. The INSERT statement is used to table those rows and add them to a history table.

Uses for Triggers

Here is some common uses for triggers:

Complex Auditing

You can use triggers to track changes made to tables. In we example above, changes made to the WorkOrder table is recorded a transactionhistory table.

Typically when creating audit trails, you'll use after triggers.

Think this is redundant, as many changes be logged in the databases journals, but the logs was meant for database  Recovery and aren ' t easily accessible by user programs. The TransactionHistory table is easily referenced and can being incorporated into end user reports.

Enforce Business Rules

Triggers can used to inspect all data before a DML action is performed. You can use INSTEAD of triggers to "intercept" the pending DML operation, apply any business rules, and ultimately complet E The transaction.

A example business rule could be, a customer status is defined as:

• Gold–purchases over $1,000,000 in the past months.
• Silver–purchase of $500,000 to $1,000,000 in the past months.
• Bronze–all other purchase levels.

An INSTEAD of trigger could was defined to check the customer status each time a customer record is added or modified. The status check would involve creating a sum of all the customers ' purchases and ensuring the new status corresponds with The sum of the last months of purchases.

Derive Column Values

Triggers can used to calculate column values.  For instance, the customer is wish to maintain a totalsales column on the customer record. Of course, for the to remain accurate, it would has to be update every time a sales is made.

This could is done using a after trigger on INSERT, UPDATE, and DELETE statements for the Sales table.

Triggers is tricky!

In general, my advice are to avoid using triggers unless absolutely necessary.

You should avoid using triggers on place of built in features. For instance, rather than rely on triggers to enforce referential integrity, you ' re better off using relationships.

Here is some reasons why I shy away from them:

1. They can is hard to troubleshoot.
2. Triggers can cause other Triggers-to-fire.  The Tables, A and B, both has an after UPDATE trigger. If the After UPDATE trigger in table A updates table B, then updating table A causes it's trigger and then B's trigger to Fire.
3. You have the to being sure you don't create a trigger storm!  Can Imagine if Table B, for some reason, updated Table A?  Now you have a circular reference ... boom!
4. I try to move as much logic into Stored procedures and has applications make changes to the database through them rather than straight up SQL statements.

What is a Database Trigger?