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.

SQL Triggers

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

🏅www.etrade.com Login Guide|✅US Etrade Tips And Tricks

Are you looking for some information related to Etrade login, or simply you want the guidance for Etrade login, or you just...

👉Best 2020 GUIDE For merrickbank.com login|All about Merrick Bank Login Tips and Tricks

If you want to do a successful Merrick Bank Login and if you do not understand the complete step-by-step login processes,...

👉Everything about Avid Login|✅Best Guidelines|💯For Amazing 2020

Are you looking for Avid login guidelines, or you just want the information related to Avid, or else you have some query...

👉Everything about MyVanillaDebitCard- Application, Activation & Benefits-[✅Updated 2020]

One of the major features MyVanillaDebitCard provides is that it makes cash management very simple for the...

🏅BAML Cards|✅Best Bank Of America Merrill Lynch Visa Reward Card Review 2020 {GUIDE}

Are you doubtful about the Bank Of America Merrill Lynch Card? Do you need a complete guide on the reward card? The...

Quick Tips About Intel Iris Graphics 6100 | Updated

Are you looking for the information related to Intel Iris Graphics 6100, or you just looking for Intel information to keep yourself...

Download Free Spotify Premium Apk | Spotify Mod Apk Free

Are you looking for a free spotify premium apk? If the answer the question is a yes, then you're at the right place. We've...

How To Download Xender For PC Windows 7,8,10 and Mac

Are you looking for information related to Xender application, or you looking for how to download Xender for PC, or you just...

✅Best Credit One Bank Login|🔒Secure Registration Guide|2020

Are you in search of a reliable Credit One Bank login and registration guide? If yes, then this is the place to...

🏅Everbank Login|✅Best Guidelines|For Amazing 2020

Are you looking for information about EverBank, or you just simply looking for the guidelines to log into the Everbank financial...

Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox