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
    • 352
    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 


    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
           DECLARE @id int
           SELECT @id = max(EmpID) FROM Employee 
           INSERT INTO Departments


    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

Sign up using

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: