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.
You must Sign In to post a response.
- Category: SQL Server
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
Points: 3 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 - #765541Hi
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!!!
Return to Return to Discussion Forum
