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

    How to create trigger on table to audit details newly added records

    how to create trigger on table and audit newly added records

    table having fields with(empid,empname,empemail)


    thanks
  • #765404
    hi,

    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

  • #765407
    Hi Sony thanks for response it was very helpfully to me as well as one if i insert any record how to generate email notification for specific user.

  • #765408
    Hi Sowjanya,

    Refer below:

    You want to generate email notification from Database?
    If yes try using concept of database mail. To work with this 1. you need to create an account and Profile in data base
    2. Configure email 3. Send mail

    1.
    In database Goto Object Explorer -> Management -> Database Mail -> Rightclick n select Configure Database Mail, Give details to create Profile and Account

    2. To configure email:
    sp_CONFIGURE 'show advanced', 1
    GO
    RECONFIGURE
    GO
    sp_CONFIGURE 'Database Mail XPs', 1
    GO
    RECONFIGURE
    GO

    3. To send email:
    USE msdb
    GO
    EXEC sp_send_dbmail @profile_name='TestingDatabaseMailProfile',
    @recipients='testUser@example.com',
    @subject='Test message',
    @body='This is the body of the test message.
    Congrats Database Mail Received By you Successfully.'




    Hope this will help you

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

  • #765409
    Hai Sowjanya,
    You can use the existing stored procedure inside your trigger which will send the email when the new record is inserted in the database table.
    sp_send_dbmail is the email which can be used.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com


Sign In to post your comments