Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • TRIGGER IN MSSQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 450
    Comment on it

    TRIGGERS IN MS-SQL

    Triggers are special kind of stored procedures that triggers automatically when an event occur. Triggers are written to fire when a specific event on table occurs. Following are the events that may force trigger-:

    1) DML operations

    Database manipulation operations like INSERT, UPDATE and DELETE.

    2) DDL operations

    Database definition operations like CREATE, ALTER and DROP.

    3) Database operations

    Like LOGON, LOGOFF, STARTUP, or SHUTDOWN.

    Syntax of DML trigger

    CREATE TRIGGER Trigger_Name
    ON
    Table_Name/View_Name
    FOR/AFTER/INSTEAD OF
    INSERT/UPDATE/DELETE
    AS
       Executable-statements
    GO	
    	

     

    A simple example to show a DML trigger

    Here, I am going to create a trigger that will fire after a row was inserted in a table.

    Below is the employee table for which I will create a trigger.

     

     

    Example of a DML trigger

    CREATE TRIGGER InsertInEmployee
    ON
    Employee
    AFTER INSERT -- This will fire after insert on employee
    AS
    	DECLARE @Age INT;
    	SET @Age = (SELECT Age FROM INSERTED AS i) -- INSERTED will keep the new row inserted
    	IF(@Age > 60)
    	BEGIN
    		RAISERROR ('Employee over 60 not allowed.', 16, 1);  
    		ROLLBACK TRANSACTION;  
    		RETURN   
    	END
    GO	

    Here, I have written a trigger for employee which will be fired when a new record is being inserted. This trigger will not allow to insert a record with age greater than 60.

     

    INSERT INTO Employee (Id, Name, Email, [Address], Age) VALUES (5, 'Deepak', 'd@yopmail.com', 'Dehradun', 59)

    After firing above query it will allow to insert, As age is not greater than 60.

    Record inserted.

     

    INSERT INTO Employee (Id, Name, Email, [Address], Age) VALUES (5, 'Deepak', 'd@yopmail.com', 'Dehradun', 61)

    On firing above query it will not allow to insert, As age is greater than 60.

     

    Record not inserted

 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: