How to implement trigger on table

Category: SQL Server
04 Apr 2016 05:30
Joined: 20/10/2011
Level: Bronze
Points: 87
Points: 2
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.

Joined:	20/10/2011
Level:	Bronze
Points:	87

Points:	2

#765380

05 Apr 2016 04:06

Joined:	13/07/2011
Level:	Gold
Points:	4261

Points:	4

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
07 Apr 2016 12:14
Joined: 13/06/2007
Level: Gold
Points: 20603

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
15 Apr 2016 10:02
Joined: 20/03/2013
Level: Gold
Points: 4203
Points: 2
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
18 Apr 2016 04:46
Joined: 14/04/2016
Level: Silver
Points: 118
Points: 4
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!!!

Joined:	14/04/2016
Level:	Silver
Points:	118

How to implement trigger on table

Email subscription