SQL Triggers and Stored Procedures

-

 

Trigger:

  • A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs.
  • Trigger are nothing but logic, which execute before insert, update and delete.
  • They help us to executes preprocessing logic.


Types of Triggers:

1. AFTER Trigger :

  • AFTER triggers are fired after the SQL server finishes execution of actions like insert, update and delete fired.  
  • Executes after the data modification.

2. INSTEAD OF Trigger :

  • INSTEAD OF triggers are fired before the SQL server starts execution of actions like insert, update and delete gets fired. 
  • Executes prior to data modification. 


Creating Triggers  :

 1. Select New Trigger from table :

 2. Create trigger :

 2. Execute trigger :

 


Stored Procedures :

  • Stored Procedures is a block code which we can re-use.
  • Stored procedures are pre-compiled, their syntax and plans are already checked, so it increases the performance.


Inline SQL :

  • Inline query is a query within the from clause.
  • SQL plan, Syntax check are executed each and everytime when we fire a query.


Creating Stored Procedure : 

1) Select Programmability => Stored Procedures => New => Stored Procedures  : 


2) Create Procedure :

3) Execute Stored Procedures :


Types of Stored Procedure :

1) System Defined Stored Procedure : 

  • These stored procedures are already defined in SQL Server. 
  • Eg. sp_rename

2) Extended Procedure : 

  • These procedures provide an interface to external programs for various maintenance activities.
  • Eg. xp_logininfo

3) User-Defined Stored Procedure : 

  • These procedures are created by the user for own actions.

4) CLR Stored Procedure : 

  • These procedure are special type of procedure that is based on the CLR (Common Language Runtime) in .net framework.



Thank You!!!


For more understanding watch below video :

Comments

Post a Comment

Popular posts from this blog

Day 3 : JavaScript

-
Image
 Data Types of JavaScript:  In JavaScript, their are three data types present i.e number, string and boolean. As we all know JavaScript is a dynamic language, means one variable can hold different datatypes.                    Example :                                      var x;                                      x=10;              //number                                      x="Hello"      //string                     // In above eg. the x holds number as well as string but shows string.                 ...
Read more

SQL Concepts-1

-
Image
              In this blog we are going to understand the important and basic concepts of SQL... Q. How to  create a SQL server database  connection and new database? 1. Firstly open SSMS and connect with server also ensure SQL Server(MSSQLSERVER) is in running mode - 2. Create a new database -                                                              3. Create a table - // U need to refresh whenever u add a new table            Q. What is primary key and foreign key in SQL server? Primary Key :  Primary key is a column having unique record. Primary key cannot have NULL values. It is used to uniquely identify records in relational database. Only one primary key is allowed in a table. When we delete or update records, data integrity happens. Foreign Key...
Read more

ASP .NET Session Management

-
Image
  Q. Why http protocol is called as stateless ? HTTP protocol is called as stateless because the http server not keeps the record of any information. When client requests to server, every time request treated independently(it not checks whether it requested before or not) by server. We can also say that when client ends the process of any task the connection between server and browser also terminated. In below example we can see that after accessing browser when we again access the value of i starts again from 0.                                                   Stateless Protocol : Stateless protocols are the protocols in which client sends request to server and server response back to client based on current state.  Server not require to maintain information of each client with multiple requests. Some important features of stateless protocol are :...
Read more