You must Sign In to post a response.
  • Category: SQL Server

    About Trigger in sql server

    Hi All,


    What is trigger?In what situation,triggers are used?
    May I know about it.


    Thanks in advance
    Nwe Nwe
  • #723922
    Hi,

    Trigger is a set of SQL statmenet similar to stored procedure. But it fires implicitly when you do any insert/update/delete operation on the table where trigger is associated with.

    Using trigger you can cascade the action to its depended table automatically.

    There are two types of triggers in SQL 2005.

    1. DML triggers
    2. DDL triggers

    Below are DML triggers,
    1. AFTER Triggers - Triggers executed after the INSERT, UPDATE, or DELETE statement.
    2. INSTEAD OF Triggers - This will be used in place of the normal triggering action.
    3. CLR Triggers - this can be either AFTER or INSTEAD trigger

    DDL Triggers,
    This will used along with CREATE, ALTER, DROP, and other DDL statements.


    Also refer below MSDN article for more detailed explanation,

    I request you to go through below tutorial if you want to learn in-depth of triggers,
    http://www.sqlteam.com/article/an-introduction-to-triggers-part-i
    http://msdn.microsoft.com/en-us/library/ms178134(v=sql.90).aspx


    Regards,
    Asheej T K

  • #723927
    Hi,

    Trigger is a special kind of stored procedure that executes when certain events like Insert, Update, Delete occurs in your table.

    When a user is modifying a table by either Inserting, Updating and Deleting the records and if you want to restrict the operation or perform some other task in the table then you can opt for Triggers in this case.

    Apart from the DDL and DML triggers, we have logon triggers too in SQL Server 2012.

    Logon triggers are executed when a user session starts after loggin in and a certain authentication mechanism is to be implemented.

    Example:

    CREATE TRIGGER trgTest
    ON YourTable
    AFTER INSERT, UPDATE, DELETE
    AS
    -- Your SQL Statements here.
    GO

  • #723948
    Hi,

    A trigger is a kind of a store procedure that executes in response to DMl action on the table like insertion, deletion or updation of data. Triggers are executed automatically.Triggers are bound to a table.

    SQL server supports 3 types of triggers

    1. DML triggers
    2. DDl triggers
    3. Logon Triggers


    DDL triggers are fired when DML operations like INSERT, UPDATE and DELETE occur.

    These DML triggers can again divided into

    1.After Triggers(Also called as For triggers) : These triggers fire after the DML statements complete execution

    CREATE TRIGGER tr_employee_forInsert
    ON tblemployee
    for Insert
    AS
    BEGIN

    -- sqllogic

    END


    2.Instead Of triggers : The Insert, Update and Delete statements causes instead of trigger fire Instead of respective statement.

    create trigger tremployee_insteadofdelete
    on tblemployee
    InsteadOf Delete
    As
    BEGIN

    ---sql logic

    END

    Aparna

  • #724011
    Trigger is used perform operation in SQL server after insert values or update values in to the table.
    Check the below code for understand concept. I have passed Basic pay values, HRA only after values inserted the total amount is update because i write trigger event for update values
    Table like this

    create table emp12
    (ID int primary key identity(1,1),
    ename varchar(30),
    BasicPay int,
    HRA int,
    Total int)


    Trigg

    CREATE TRIGGER [dbo].[UpEmpSal]
    ON [dbo].[emp12]
    AFTER INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @i INT
    Select @i = ID FROM Inserted
    update emp12 set Total=BasicPay+HRA where ID = @i
    END

    //Insert into table like this

    insert into emp12(ename,BasicPay,HRA) values('ravi','500','500')

    Regards
    N.Ravindran
    Your Hard work never fails

  • #734770
    Trigger

    A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

    Trigger Example




    create trigger tri_update on Emp
    for update
    as
    begin
    if update(EmpID)
    begin
    print'not update here'
    rollback transaction
    end
    end


    if you try to update like this
    update Emp set EmpID='jj' where UserName='mahagr'

    Error will be display like this:

    not update here
    Msg 3609, Level 16, State 1, Line 1
    The transaction ended in the trigger. The batch has been aborted.



    Thanks & Regards
    G.Renganathan
    Nothing is mine ,Everything is yours!!!


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.