Friday, 13 July 2018

SQL Server Trigger

Trigger is a special type of stored procedure ,which automatically executes with an event occurs in the database . A Triggers execute automatically when modifying data in a database table through  data manipulation language (DML) .Trigger fires when  INSERT, UPDATE,  DELETE is executed  on a table or view.

 Here , we have created two table and inserted data to a table for example purpose.

CREATE TABLE student
(
             id INT NOT NULL,
             name VARCHAR (500) NOT NULL,
             age INT NOT NULL,
             adress VARCHAR (500) NOT NULL,
             date_of_birth DATETIME NOT NULL,
             height DECIMAL(10,2) NOT NULL
)

CREATE TABLE student2
(
            id INT NOT NULL,
             name VARCHAR (500) NOT NULL,
             age INT NOT NULL,
             adress VARCHAR (500) NOT NULL,
            date_of_birth DATETIME NOT NULL,
            height DECIMAL(10,2) NOT NULL
)

INSERT INTO student(id,name,age,adress,date_of_birth,height)
SELECT 1,'John1',12,'34,student street','01/08/2018',10.5
UNION
SELECT 1,'John2',13,'35,student street','02/08/2018',11
UNION
SELECT 1,'John3',14,'36,student street','03/08/2018',11.5
UNION
SELECT 1,'John4',15,'37,student street','04/08/2018',12
UNION
SELECT 1,'John5',16,'38,student street','05/08/2018',12.5
UNION
SELECT 1,'John6',17,'39,student street','06/08/2018',13.5
UNION
SELECT 1,'John7',18,'40,student street','07/08/2018',14
UNION
SELECT 1,'John8',19,'41,student street','08/08/2018',14.5
UNION
SELECT 1,'John9',20,'42,student street','09/08/2018',15
UNION
SELECT 1,'John10',21,'43,student street','10/08/2018',15.5
UNION
SELECT 1,'John11',22,'44,student street','11/08/2018',16


There are two kind of trigger  

1)After trigger (AFTER CLAUSE) 
1)Instead of trigger (INSTEAD OF CLAUSE) 



After trigger (AFTER CLAUSE)
The After trigger  fires after SQL Server completed  the DML execution 

Syntax

CREATE TRIGGER Trigger_Name
ON TBALE_NAME
AFTER DML(INSERT /UPDATE/DELETE)
AS
BEGIN
/* Statment*/
END

Example

CREATE TRIGGER INSERTT
    ON STUDENT
    AFTER INSERT
    AS
    BEGIN
           INSERT INTO student2 (id,name,age,adress,date_of_birth,height)
              SELECT id,name,age,adress,date_of_birth,height FROM student
    END

We have two table , student table 12 row and student2 have no row.Now we have created the trigger.
The trigger will fire, when a data will be inserted to student table.We have written the script to insert data to student table.

INSERT INTO student (id,name,age,adress,date_of_birth,height)
SELECT 5,'john25',22,'44,School Steet',GETDATE(),15

Now execute the statement.We will see the following output.

(12 row(s) affected)

(1 row(s) affected)

Now we query on student2.
 
SELECT * FROM student2

Output:
id  name   age  adress        date_of_birth        height 
1    John1    12    34,student street    2018-01-08 00:00:00.000    10.50
1    John10   21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11   22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00



Instead of Trigger (INSTEAD OF CLAUSE)
The Instead of Trigger fires before SQL Server starts the execution.

Syntax  

CREATE TRIGGER Trigger_Name
ON TBALE_NAME
INSTEAD OF DML(INSERT /UPDATE/DELETE)
AS
BEGIN
/* Statment*/
END
Example
CREATE TRIGGER INSERTT
ON STUDENT
INSTEAD OF INSERT
AS
BEGIN
        INSERT INTO student2 (id,name,age,adress,date_of_birth,height)
        SELECT id,name,age,adress,date_of_birth,height FROM student
END

We have deleted student2 table data.Now we have created the above trigger.Now we have write the following insert statement.

INSERT INTO student (id,name,age,adress,date_of_birth,height)
SELECT 5,'john25',22,'44,School Steet',GETDATE(),15

Now execute the statement.We will see the following output.
 
12 row(s) affected)

(1 row(s) affected)


Now we query on student2.
 
SELECT * FROM student2

Output:
id  name   age  adress        date_of_birth        height 
1    John1    12    34,student street    2018-01-08 00:00:00.000    10.50
1    John10   21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11   22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00 


You ca write trigger on all three DML statement (INSERT/UPDATE/DELETE). Here is example of DELETE statement trigger.

Eaxmple 1 (AFTER CLAUSE)

CREATE TRIGGER INSERTT
ON STUDENT
   AFTER DELETE
AS
BEGIN
         INSERT INTO student2 (id,name,age,adress,date_of_birth,height)
         SELECT id,name,age,adress,date_of_birth,height FROM student
END

Execute the delete statement.

DELETE FROM student WHERE age=12

Table data output
SELECT * FROM student2

id  name   age  adress        date_of_birth        height  
1    John10    21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11    22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00 

 
Eaxmple 2 (INSTEAD OF CLAUSE)
 
CREATE TRIGGER INSERTT
ON STUDENT
   INSTEAD OF DELETE
AS
BEGIN
         INSERT INTO student2 (id,name,age,adress,date_of_birth,height)
         SELECT id,name,age,adress,date_of_birth,height FROM student
END

Execute the delete statement.

DELETE FROM student WHERE age=12

Table data output
SELECT * FROM student2

id  name   age  adress        date_of_birth        height  
1    John10    21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11    22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street    2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street    2018-03-08 00:00:00.000    11.50
1    John4    15    37,student street    2018-04-08 00:00:00.000    12.00
1    John5    16    38,student street    2018-05-08 00:00:00.000    12.50
1    John6    17    39,student street    2018-06-08 00:00:00.000    13.50
1    John7    18    40,student street    2018-07-08 00:00:00.000    14.00
1    John8    19    41,student street    2018-08-08 00:00:00.000    14.50
1    John9    20    42,student street    2018-09-08 00:00:00.000    15.00
5    john25    22    44,School Steet    2018-07-13 12:58:53.253    15.00


 
- No comments:
Labels:

SQL Server Stored Procedure

Stored Procedure do a particular task a relational database management system .Stored Procedure contain group of statement execute in proper order.Generally Stored Procedure is used for insert , update , delete data to the database .Stored Procedure  is a part of a database system which is very fast in execution, as it is per-compiled and execution path is predefined. Stored Procedure is called by on or more application connected to the database. Stored Procedure support multiple  call. Stored Procedure accept input parameter, support output parameter and parameter accept value as null also.


Syntax :

USE Database_Name
GO

CREATE PROCEDURE StoreProcedureName
AS
/* Stament /query */
GO


Here , we have created a table and inserted data to the table for example purpose.

CREATE TABLE student
(
          id             INT NOT NULL,
          name           VARCHAR (500) NOT NULL,
          age            INT NOT NULL,
          adress         VARCHAR (500) NOT NULL,
          date_of_birth  DATETIME NOT NULL,
          height         DECIMAL(10,2) NOT NULL
)


                INSERT INTO student(id,name,age,adress,date_of_birth,height)
                 SELECT 1,'John1',12,'34,student street','01/08/2018',10.5
                 UNION
                 SELECT 1,'John2',13,'35,student street','02/08/2018',11
                 UNION
                 SELECT 1,'John3',14,'36,student street','03/08/2018',11.5
                 UNION
                 SELECT 1,'John4',15,'37,student street','04/08/2018',12
                 UNION
                 SELECT 1,'John5',16,'38,student street','05/08/2018',12.5
                UNION
                SELECT 1,'John6',17,'39,student street','06/08/2018',13.5
                UNION
                SELECT 1,'John7',18,'40,student street','07/08/2018',14
                UNION
                SELECT 1,'John8',19,'41,student street','08/08/2018',14.5
                UNION
               SELECT 1,'John9',20,'42,student street','09/08/2018',15
               UNION
               SELECT 1,'John10',21,'43,student street','10/08/2018',15.5
               UNION
               SELECT 1,'John11',22,'44,student street','11/08/2018',16


1)Here is a example of Stored Procedure.

 
CREATE PROCEDURE MyStoreProcedure
AS
    SELECT TOP 5 * FROM student
GO

The above store procedure name is "MyStoreProcedure", it will return top 5 rows from  student table when executed.Below is the syntax for execution.

Execute MyStoreProcedure

Output:
id   name    age   adress                    date_of_birth                     height
1    John1    12     34,student street    2018-01-08 00:00:00.000    10.50
1    John10   21    43,student street    2018-10-08 00:00:00.000    15.50
1    John11   22    44,student street    2018-11-08 00:00:00.000    16.00
1    John2    13    35,student street     2018-02-08 00:00:00.000    11.00
1    John3    14    36,student street      2018-03-08 00:00:00.000   11.50







2)Here is a example of Store Procedure with Multiple Parameter

CREATE PROCEDURE MyStoreProcedure
      @in_age INT = NULL,
      @in_name VARCHAR(50) = NULL
AS
SELECT * FROM student WHERE age=@in_age OR name=@in_name
GO

The above srored procedure accept parameter age and name.Retun 
only those record which match.Here is the execution of the procedure.
 
Execute MyStoreProcedure 12,'John5'

 Output:
id   name   age   adress               date_of_birth              height
1  John1    12    34,student street    2018-01-08 00:00:00.000    10.50
1  John5    16    38,student street    2018-05-08 00:00:00.000    12.50


3)Here is a example of Store Procedure with Output Parameter
 
CREATE PROCEDURE MyStoreProcedure
             @in_age INT= NULL,
             @in_age_in_month INT= NULL OUTPUT
AS
SELECT @in_age_in_month=@in_age*12 FROM student WHERE age=@in_age
GO

The above procedure accept age and another variable declare for output.When execute query,the variable(@out_age_in_month) fill will data from stored procedure and print the result.

DECLARE @out_age_in_month INT
Execute MyStoreProcedure 12,@out_age_in_month OUT


PRINT(@out_age_in_month)

Output:
144
 
 
- No comments:
Labels:
Subscribe to: Posts (Atom)

বাঙালির বেড়ানো সেরা চারটি ঠিকানা

  বাঙালি মানে ঘোড়া পাগল | দু একদিন ছুটি পেলো মানে বাঙালি চলল ঘুরতে | সে সমুদ্রই হোক , পাহাড়ি হোক বা নদী হোক। বাঙালির ...

  • 020 Weekend Tour Chandipur
    Another weekend Getaway of Odisha  is Balasore, commonly known as Baleshwar . Balasore is  a district  of Odisha,India. Chandipur : Chandi...
  • 019 C# Garbage Collcetion
    Garbage collection                    Garbage collection is a technique to recover the memory consumed by some unused variable and objec...
  • What is json.stringify ?
    What is json.stringify ? If the stringify method sees an object that contains a toJSON method, it calls that method, and stringifies the ...