Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Triggers in the SQL server

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 99
    Comment on it

    A trigger is a unique store procedure that is executes to give a response to particular action on the table of a database using  the following SQL statements:


    1. Data Manipulation Language (DML) SQL Statements (like INSERT, UPDATE or DELETE).
    2. A database definition (DDL) statement (CREATE, ALTER, or DROP).
    3. A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).


    Basically, Trigger is like a database object which works with a tables and execute significantly i.e. we can not execute the trigger explicitly.

     

       We have a two types of trigger:
       1.  After Triggers (For Triggers)--> It is used to executes after DML SQL statements.
        2. Instead Of Triggers --> To executes instead of actual DML statements.

     

    Syntax for creating a Trigger:-

    CREATE [OR REPLACE ] TRIGGER trigger_name 
    ON table_name 
    {AFTER | INSTEAD OF } 
    {INSERT [OR] | UPDATE [OR] | DELETE} 
    AS
    DECLARE
       Declaration-statements
    BEGIN 
       Executable-statements
    END;
    

     

    For Example:-


    In this example we illustrate how to create a trigger in sql. We are creating a two tables  Employee with the column (EmpID,empName,empSalary and empDesignation) and Department with the column(DeptID,deptName,EmpID). After creating tables we are creating a trigger on the Employee table that will execute after performing an INSERT operations on the Employee table. This trigger will insert a new record in Department table corresponding to the EmpID of a new record inserted in Employee table.

    CREATE TRIGGER InsertDepartment
           ON Employee
           AFTER INSERT
    AS
    BEGIN
           DECLARE @id int
           SELECT @id = max(EmpID) FROM Employee 
           INSERT INTO Departments
           (deptName,EmpID)
           VALUES
           ('Management',@id)
    END
    

     

    To understand the above code how the trigger is working, We need to perform INSERT operation on Employee table.

    INSERT INTO Employee (empName,empSalary and empDesignation)
    VALUES ('Kritika', 24,7500.00,'HR' );


    Now the trigger InsertDepartment will execute automatically and add a new record in Department table.

     

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: