How to create trigger on table to audit details newly added records
how to create trigger on table and audit newly added recordstable having fields with(empid,empname,empemail)
thanks
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
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.'