A trigger is a special kind of stored procedure
that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify
data through a data manipulation language (DML) event. DML events are INSERTING,
UPDATE, or DELETE statements on a table or view.
Type of Trigger
Ø
DML trigger
Ø
DDL trigger
Ø
Log on trigger
Ø
CLR Triggers
DML Trigger
There are two type of DML trigger. It is faired
automatically in response to DML (Insert, Update and delete) event.
Ø
After Trigger (For
trigger)
Ø
Instead of Triggers
After Triggers
These
triggers are executed after an action such as Insert, Update or Delete is
performed. For example If we insert
record/row in a table then the trigger associated with the insert event on this
table will fire only after the row passes all the checks, such as primary key,
rules, and constraints. If the record/row insertion fails, SQL Server will not
fire the After Trigger.
Let see the
example
I have a
table
CREATE TABLE [EMP](
[ID]
[int] IDENTITY(1,1) NOT NULL,
[Name]
[varchar](50) NULL,
[empAdd]
[varchar](50) NULL,
[Mobile]
[varchar](10) NULL
)
|
Inserting
some records some
Now I am creating an audit table which stores the
information of insert, update and delete.
create table tblAuditEmp
(
ID INT IDENTITY (1,1),
Audit_Desc
varchar(1000)
)
|
This table has all audit info.
Now I am going to create an insert trigger on EMP
tale.
create trigger trg_Emp_insert
on EMP
for insert
as
begin
select * from inserted
end
|
Trigger
has been created. Now I am inserting
Inserted
table
INSERTED logical table will hold the rows which are
inserted by the INSERT and UPDATE statement.
Now I am writing the script to insert record in
audit table.
http://bageshkumarbagi-msbi.blogspot.in/2017/08/inserted-and-deleted-logical-tables-in.html
Create trigger trg_Emp_insert
on EMP
for insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
@emp_Mob
varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd
=i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Inserted Record
,trg_Emp_insert.';
BEGIN
SET @str = 'Employee ID '+ cast(@emp_id as varchar) +
' is inserted on '+
cast(getdate() as varchar) +' Employee Name : ' +
@emp_name
+ 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + ' ' +
@audit_action;
Insert into tblAuditEmp (Audit_Desc) values (@str);
END
|
There is no record in audit table
Now I am inserting a record in emp table.
Record inserted
Now I am seeing the Audit table.
Trigger for Update
Now I am
creating a new trigger for update.
Create trigger trg_Emp_update
on EMP
for Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
@emp_Mob
varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd
=i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Updated Record
,trg_Emp_update.';
BEGIN
SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is updated on '+ cast(getdate() as varchar) +' Employee Name : '
+ @emp_name
+ 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + ' ' +
@audit_action;
Insert into tblAuditEmp (Audit_Desc) values (@str);
END
|
Updating
the record.
Now see
the records in audit table.
Trigger for Delete
When we deleting the record it will execute and
insert record in audit table.
Create trigger trg_Emp_Delete on EMP
for delete
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
@emp_Mob
varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from deleted i;
select @emp_name=i.Name from deleted i;
select @emp_empadd
=i.empAdd from deleted i;
select @emp_Mob = i.Mobile from deleted i;
SET @audit_action='deleted Record
,trg_Emp_update.';
BEGIN
SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is deleted on '+ cast(getdate() as varchar) +' Employee Name : '
+ @emp_name
+ 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + ' ' +
@audit_action;
Insert into tblAuditEmp (Audit_Desc) values (@str);
END
|
Now I am deleting the record from emp table.
Deleted
table
DELETED logical table will hold the rows which are
deleted from the trigger table (i.e. the table on which the trigger is defined)
by the DELETE.
Read: INSERTED and
DELETED Logical Tables in Sql Server
Instead of Triggers
These
triggers are executed instead of any of the Insert, Update or Delete operations.
For example, let’s say we write an Instead of Trigger for Delete
operation, and 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.
Delete Trigger
Create trigger trg_Emp_Delete_Instead_of on
EMP
Instead of delete
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
@emp_Mob
varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from deleted i;
select @emp_name=i.Name from deleted i;
select @emp_empadd
=i.empAdd from deleted i;
select @emp_Mob = i.Mobile from deleted i;
SET @audit_action='deleted Record ,trg_Emp_update.';
BEGIN
SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is deleted on '+ cast(getdate() as varchar) +' Employee Name : '
+ @emp_name
+ 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + ' ' +
@audit_action;
Insert into tblAuditEmp (Audit_Desc) values (@str);
END
|
Trigger update
Create trigger trg_Emp_update_Instead_of on
EMP
Instead of Update
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
@emp_Mob
varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd
=i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Updated Record ,trg_Emp_update.';
BEGIN
SET @str = 'Employee ID '+ CAST( @emp_id as VARCHAR) + ' is updated on '+ cast(getdate() as varchar) +' Employee Name : '
+ @emp_name
+ 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + ' ' +
@audit_action;
Insert into tblAuditEmp (Audit_Desc) values (@str);
END
|
Trigger Insert
Create trigger trg_Emp_insert_Instead_of on
EMP
Instead of insert
AS
declare @emp_id int, @emp_name varchar(55), @emp_empadd varchar(100),
@emp_Mob
varchar(100), @audit_action varchar(100),@str VARCHAR(1000);
select @emp_id=i.ID from inserted i;
select @emp_name=i.Name from inserted i;
select @emp_empadd
=i.empAdd from inserted i;
select @emp_Mob = i.Mobile from inserted i;
SET @audit_action='Inserted Record ,trg_Emp_insert.';
BEGIN
SET @str = 'Employee ID '+ cast(@emp_id as varchar) + ' is inserted on '+ cast(getdate() as varchar) +' Employee Name : '
+ @emp_name
+ 'Employee Add : ' + @emp_empadd + ' Mobile : '+ @emp_Mob + ' ' +
@audit_action;
Insert into tblAuditEmp (Audit_Desc) values (@str);
END
|
Thanks for sharing valuable information. Your blogs were helpful to tableau learners. I
ReplyDeleterequest to update the blog through step-by-step. Also, find the tableau news at
Tableau Online Course Blog.
Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
ReplyDeletesql dba training
sql server dba online training
Through this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
ReplyDeleteMsbi online training Hyderabad
Msbi online training India
Msbi online course
Msbi course
Msbi training
Msbi certification training
Say, you got a nice article post .Really thank you! Really Great.
ReplyDeletedata science training
python training
angular js training
selenium trainings