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

    Triggers

    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]
    AFTER INSERT, UPDATE
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @id INT
    Select @id = ID FROM Inserted
    update tbl1 set total=val1+val2 where id = @id
    END

    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
    Example:-

    create trigger tri_update on Emp
    for update
    as
    begin
    if update(EmpID)
    begin
    print'You can not able to update'
    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:

    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
    G.Renganathan
    Nothing is mine ,Everything is yours!!!

  • #576760
    [Response removed by Admin. Read forum policies.]
    Thanks/Regards,
    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


    Bunty

  • #576787
    http://msdn.microsoft.com/en-us/library/ms189799.aspx
    Regards,
    Programmer

    http://www.dotnetutorial.com/interview-questions.aspx?catid=1

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