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


    Explain about triggers briefly.. And in which situation the trigger are to be used. Please explain with example
  • #576756
    A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table.

    The following example will calculate the total of value1 and value2 and insert into total when a record is inserted or updated:

    create table tbl1
    (ID int primary key identity(1,1),
    val1 int,
    val2 int,
    total int)

    CREATE TRIGGER [dbo].[UpdateTotal]
    ON [dbo].[tbl1]
    Select @id = ID FROM Inserted
    update tbl1 set total=val1+val2 where id = @id

    insert into tbl1(val1,val2) values(21,31)

    Thanks & Regards
    Paritosh Mohapatra
    Microsoft MVP (ASP.Net/IIS)
    DotNetSpider MVM

  • #576757
    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.

    Types of trigger:-
    1)DML Trigger
    DML Trigger have two types one is after trigger and another one is instead of trigger.
    DML Trigger is fired only when INSERT, UPDATE, and DELETE Statement occurs in table.

    2)DDL Trigger

    create trigger tri_update on Emp
    for update
    if update(EmpID)
    print'You can not able to update'
    rollback transaction

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

    Error will be display like this:

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

    Thanks & Regards
    Nothing is mine ,Everything is yours!!!

  • #576760
    [Response removed by Admin. Read forum policies.]
    Bhavin Patel

    Please reply to this post

  • #576767
    A trigger is a block of code that constitutes a set of T-SQL statements that are activated in response to certain actions or conditions.

    A trigger can also be interpreted as a special kind of stored procedure that is executed whenever an action,such as data modification,takes place.

    A Trigger is fired whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements -INSERT,UPDATE,OR DELETE.

    Triggers help in maintaining consistent,reliable,and correct data in tables.They enable the performance of complex actions and cascade these actions to other dependent tables.

    Thanks & Regards


  • #576787

  • #576857
    [Response removed by Admin. Read forum policies.]

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