Introduction to triggers in sql server 2008

Introduction:

What is triggers in sql server?

In this tutorial, we will explain you a brief overview of SQL triggers tutorial in sql server 2008 with example.

SQL Triggers are programme, which are self-executed when some events occur.

A SQL trigger is executed or fired whenever following some events occurs e.g

  1. Database manipulation Language Statement (DELETE, INSERT, or UPDATE)
  2. Database definition language statement (CREATE, ALTER, or DROP).
  3. Database operation (SERVERERROR, STARTUP, or SHUTDOWN).

 

A SQL Trigger is special kind of stored procedure. It is called special because it executed automatically when DML event is made against a table.

 

It is important to understand SQL trigger’s advantages and disadvantages. In the following tutorial, we will discuss the advantages and disadvantages of using SQL triggers.

Advantages of using SQL triggers

SQL triggers used to check the integrity of data.

SQL triggers used to catch errors in business logic in the database layer.

  • Creating some derived column values automatically
  • Event logging and keeping information on table entry
  • SQL triggers are very useful to audit data

Creating trigger in SQL

The syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF colname] ON tablename

[REFERENCING OLD AS o NEW AS n] [FOR EACH ROW]

WHEN (condition)

DECLARE

// Declare

BEGIN

//statements

EXCEPTION

Exception-handling-statements

END;

Sql Trigger syntax explanation:

  • CREATE [OR REPLACE] TRIGGER: This syntax is used to create new trigger or replaces an existing trigger with the trigger_name.
  • {BEFORE | AFTER | INSTEAD OF}: BEFORE / AFTER indicate when the trigger would be executed. To create trigger on a view INSTEAD OF clause is used.
  • {INSERT [OR] | UPDATE [OR] | DELETE}: Database manipulation Language Statement used after {BEFORE | AFTER | INSTEAD OF} indicating which DML operation Executed trigger.
  • [OF col_name]: This define the column name that would be updated.
  • [ON table_name]: This define the name of the table related with the trigger.
  • [REFERENCING OLD AS o NEW AS n]: This grant you to refer new and old values for different DML statements, like INSERT, UPDATE, and DELETE.
  • [FOR EACH ROW]: This define a row level SQL trigger (the trigger would be executed for every one row being affected or the trigger will execute just once when the SQL is executed) which is called a table level trigger.
  • WHEN (condition): This provides a condition when the trigger would fire. This section is valid only for row level triggers.

 

SQL Trigger Example:

Here we take customer table its consist of (ID,NAME,AGE,ADDRESS,SALARY)

The following SQL Code creates a row level trigger for the customer table that would executed if INSERT or UPDATE or DELETE operations performed on the CUSTOMER table. This trigger will store the salary difference between the old values and new values:

 

CREATE OR REPLACE TRIGGER DisplayChanges

BEFORE DELETE OR INSERT OR UPDATE ON customer

FOR EACH ROW WHEN (NEW.ID > 0)

DECLARE   Diff number;

BEGIN   Diff: =: NEW. Salary – :OLD. Salary;

dbms_output.put_line(‘Diffrence : ‘ || :Diff);

END;

In this Example for Trigger we used various types of trigger like before insert trigger  , before delete trigger and before update trigger .My next post will focus on After update trigger.

You may also interested in my previous post Cascading drop down using asp.net and sql stored procedure .

Check Similar post – SQL queries interview questions and answers , SQL query join tables,Group by clause

Leave a Comment