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

    How to implement trigger on table

    i have one table having fields(empno,empname,emaild).

    how to create trigger if i did any insert,delete transactions on this table.

    Advance thanks,

    sowjanya.
  • #765380
    Hi Sowjanya,

    Refer the below...
    Audit Trial or Trigger to get records for each operation of insert, update, delete of other table.

    CREATE TABLE [dbo].[T1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    [address] [varchar](500) NOT NULL,
    [companyname] [varchar](100) NOT NULL
    ) ON [PRIMARY]

    CREATE TABLE [dbo].[T2](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [type] [varchar](50) NULL
    ) ON [PRIMARY]

    Now i want to enter for each insert, update, delete operation on T1.
    so craete trigger on T1.

    CREATE TRIGGER [dbo].[TriT1] on [dbo].[T1] for insert, update, delete
    as

    declare @insRecord int,@Type char(8) ,@delRecord int
    BEGIN
    SET NOCOUNT ON;
    select * into #ins from inserted
    select * into #del from deleted
    select @insRecord = count(1) from #ins
    select @delRecord = count(1) from #del
    if @insRecord > 0
    if @delRecord > 0
    select @Type = 'Update'
    else
    select @Type = 'Create'
    else
    select @Type = 'Delete'
    -- Insert statements for trigger here
    if @insRecord >0 or @delRecord >0
    begin
    insert into T2 values (@Type )
    end
    END
    GO


    Hope this will help you

    Regards,
    SonyShiva
    Never lose hope..You never know what tomorrow will bring

  • #765398
    A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server, DML triggers executes when user perform any DML operation like Insert /update/delete.
    look at the below snippet I which, DML trigger fired with a reminder message, when there is insert or update operation executes
    CREATE TRIGGER reminder1
    ON Sales.Customer
    AFTER INSERT, UPDATE
    AS RAISERROR ('Notify Customer Relations', 16, 10);
    GO
    hope it helps

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #765510
    Create Trigger trigername on Tablename is the syntax for the trigger. Search with my name as I wrote one article regarding this.
    SRI RAMA PHANI BHUSHAN KAMBHAMPATI

  • #765541
    Hi

    Triggers are database operations which are automatically performed when an action such as Insert, Update or Delete is performed on a Table or a View in database.
    Triggers are associated with the Table or View directly i.e. each table has its own Triggers.

    Types of Triggers
    There are two types of Triggers. After and Instead of Triggers.
    After Triggers
    These triggers are executed after an action such as Insert, Update or Delete is performed.

    Instead of Triggers
    These triggers are executed instead of any of the Insert, Update or Delete operations. For example, let's say you write an Instead of Trigger for Delete operation, then whenever a Delete is performed the Trigger will be executed first and if the Trigger deletes record then only the record will be deleted.
    Insert :

    CREATE TRIGGER trgAfterInsert on Employee_Demo
    FOR INSERT
    AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);
    select @empid=i.Emp_ID from inserted i;
    select @empname=i.Emp_Name from inserted i;
    select @empsal=i.Emp_Sal from inserted i;
    set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
    values (@empid,@empname,@empsal,@audit_action,getdate());
    PRINT 'AFTER INSERT trigger fired.'


    Delete :

    CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo
    FOR DELETE
    AS
    declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;
    select @empname=d.Emp_Name from deleted d;
    select @empsal=d.Emp_Sal from deleted d;
    select @audit_action='Deleted -- After Delete Trigger.';
    insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
    values (@empid,@empname,@empsal,@audit_action,getdate());
    PRINT 'AFTER DELETE TRIGGER fired.'

    Hope it helps!!!


Sign In to post your comments