Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

What is trigger in SQL Server?

Author: Richard Wand

In this article, I will give you a good explanation of Triggers and I will also show you how you can use it.

 

A Trigger is a database object that is fired in result of DML statements. It is fired automatically when an associated DML statement like UPDATE, INSERT and DELETE is executed. Trigger is a named procedural code that is fired in response to a particular event. That particular event can be associated with a table or a view. Statements that fires Trigger and the Trigger itself are treated as single transaction and this transaction can be rolled back within the Trigger. Trigger is often called as special kind of stored procedure and it is something like stored procedure with a basic difference. Trigger fires automatically in response of certain events and we cannot invoke triggers explicitly. The only way to invoke trigger is to perform DML statement that is associated with it. Triggers cannot be called directly from your application like stored procedures. They are invoked only in a result of INSERT, UPDATE and DELETE operation. The definition of Trigger is stored in database management system and it can only be invoked by database management systems. Trigger functionality can be used inside a stored procedure to fire it in response of DML statements.
 
When a Trigger is fired in response of a DML statement, it can execute another DML statement. It means a Trigger can result of firing another Trigger. This situation is called nested Trigger. In nested Trigger, a Trigger can contain INSERT, UPDATE and DELETE statements inside it. SQL Server 2005 and later also supports Triggers for Data Definition Language which fires in response of CREATE, ALTER and DROP statements.
 
Uses and Benefits of Triggers

  • Triggers are used to restrict access to specific data.
  • They are used to implement data integrity.
  • Triggers are used when we need to perform certain actions in response of any DML statement.
  • They are helpful to enforce business rules and they can catch errors in business logic in database.
  • They can be used to perform cascading update or delete operations.
  • You can perform logging in database using Triggers.
  • Triggers can prevent inconsistent, incorrect and unauthorized changes to data.
  • They are also useful when you want to audit changes of data in table.
  • You can use Triggers to gather statistics on a table access.
 
Disadvantages of Trigger
 
  • Triggers are stored in complied form like store procedure. Trigger code is complied every time when it is invoked.
  • They execute invisibly from application which used database so sometimes it can be difficult to understand what happened. 
  • Triggers cannot be used with SELECT statement.
  • There is no version control system available for multiple versions of Triggers on a Table.

 

Types of Triggers

There are basically two types of Triggers.
 
After Trigger
Instead of Trigger
 
Both these Triggers are used with DML statements and DDL statements
 
If you know how to create stored procedure, there will no problem for creating Triggers. General syntax for Triggers can be like this:
 
CREATE TRIGGER trigger_name
ON table_name
FOR {AFTER [OR] INSTEAD OF}
    {INSERT [OR] | UPDATE [OR] | DELETE}
 WHEN (condition) 
 BEGIN
   --- SQL statements 
 END
 
After Trigger

After Trigger executes after an INSERT, UPDATE or DELETE statement on a database table. These triggers cannot be used with Views. After Triggers never fires if a constraint violation occurs. DML After Triggers are divided into further types.
 
After Insert Trigger

It executes after an INSERT statement execution.
 
Following Trigger prevent user to insert record into Products table.
 
CREATE TRIGGER PreventInsertProduct
ON Products
AFTER INSERT
AS
BEGIN
      ROLLBACK TRANSACTION
END
 
After Update Trigger
 
This Trigger fires after an UPDATE statement on database table.
 
Following Trigger don’t allow users to update product name in Products table.
 
CREATE TRIGGER PreventChangeProductName
ON Products
AFTER UPDATE
AS
IF UPDATE(ProductName)
BEGIN
    ROLLBACK TRANSACTION
END
 
After Delete Trigger

It executes after a DELETE statement on a table.
 
Following Trigger will prevent user to delete product from Products table.
 
CREATE TRIGGER PreventDeleteProduct
ON Products
FOR DELETE
AS
BEGIN
      ROLLBACK TRANSACTION
END

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< What is cursor in SQL Server?

Disclaimer - Privacy
© 2002-2017 DevASP.net