A DML (Data Manipulation Language)
trigger fires when an Insert, Update, or Delete statement is performed on the
table, view, schema, or database with which the event is associated. They are
used to evaluate data after data manipulation using DML statements.
To understand their functionality, we
need to create a Department Table in our database and will see the each action
of this trigger on this table.
Use tempdb
Go
---- Create an Example Table
Create Table
Department
(
DeptId Int
Identity (1,1),
DeptName Varchar(15) Not Null,
DeptBudget Decimal(16,2) Not Null
)
----- Insert Values into Department
Insert into
Department (DeptName, DeptBudget) Values
('IT', '1200000.00'),
('Finance', '40020000.00')
---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department
---- Create Audit Department Table which will be used
to store the data
---- Whenever trigger will fire
Create Table
AuditDepartment
(
DeptId Int,
DeptName Varchar(15) Not Null,
DeptBudget Decimal(16,2) Not Null,
AuditAction Varchar(200) Not Null,
AuditDate Datetime
default(Getdate())
)
---- Fetch data from the AuditDepartment
Select *
from AuditDepartment
|
Now, we have two tables. One is our primary
table (Department) to store the values and another one is our secondary Audit
table (AuditDepartment) to store all the audit information which is based on
our primary table. DML Triggers are the following types of DML triggers:
A) Instead
of Trigger: Instead of
trigger are fired in place of the triggering action such as an insert, update,
or delete. In ‘Instead of Trigger’ we insert the data into the virtual tables
prior to checking the constraints. As
far as ‘After Trigger’ constraints are concerned, they are checked in the first
place. Data is then inserted into the
virtual tables ( inserted and deleted tables).
Instead of Insert Trigger - First of all, we will create a Trigger
on the Department table which will be called after the insert the record into
Department table and fill these new records with the help of magic table
Inserted based on the matching condition to validate the data as shown below:
CREATE Trigger
tri_InsteadOfInsert_department On Department
INSTEAD OF
INSERT
AS
Begin
-- Set Transaction
BEGIN TRAN
-- Set Nocount on
SET NOCOUNT ON
-- check the department budget.
-- If Budget value less than 100000 then raise an error
-- otherwise insert value into Department
IF (Select DeptBudget from Inserted)<100000
begin
RAISERROR('Invalid Budget Value. Budget value should be greater
than 100000',16,1);
ROLLBACK;
end
Else
Begin
---- Insert data into main table
Insert into
Department (DeptName, DeptBudget)
Select I.DeptName,
I.DeptBudget From Inserted
I;
COMMIT;
PRINT 'Record
Inserted -- Instead Of Insert Trigger.'
End
End
---- Insert new records into Department
Insert into
Department (DeptName, DeptBudget) Values
('Sales-Marketing', '95200000.00')
Insert into
Department (DeptName, DeptBudget) Values
('Admin',
'99999.00')
---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department
|
B) After
Trigger: They always fire after SQL Server completes the execution of the
action successfully such as an insert, update, or delete.
After
Insert Trigger: First of all, we will create a Trigger on the
Department table which will be called after the insert the record into
Department table and fill these new records into AuditDepartment table with the
magic table Inserted as shown below:
---- create trigger to fire after the insert command
executes successfully
CREATE Trigger
tri_afterinsert_department On Department
FOR INSERT
AS
Begin
---- Insert Records from Inserted table into
AuditDepartment
Insert into
AuditDepartment (DeptId, DeptName, DeptBudget, AuditAction, AuditDate)
Select I.DeptId,
I.DeptName,
I.DeptBudget,
AuditAction='Record Inserted Successfully- Calling After Insert
Trigger',
AuditDate =Getdate()
From Inserted
I
Print 'Trigger
has been fired successfully after insert new records'
End
---- Insert new records into Department after trigger
is created
Insert into
Department (DeptName, DeptBudget) Values
('Sales-Marketing', '95200000.00')
---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department
---- Fetch data from the AuditDepartment
Select *
from AuditDepartment
|
After
Update Trigger: Now, we will create a Trigger on the
Department table which will be called after the update the existing record in Department
table and fill these records into AuditDepartment table with the magic table
Inserted as shown below:
---- create trigger to fire after the update command
executes successfully
CREATE Trigger
tri_afterupdate_department On Department
FOR Update
AS
Begin
---- Insert Records from Inserted table into
AuditDepartment
Insert into
AuditDepartment (DeptId, DeptName, DeptBudget, AuditAction, AuditDate)
Select I.DeptId,
I.DeptName,
I.DeptBudget,
AuditAction='Record Updated Successfully- Calling After Update
Trigger',
AuditDate =Getdate()
From Inserted
I
Print 'Trigger
has been fired successfully after update the existing records'
End
---- Update the existing record
Update Department
set DeptBudget ='145000000.00'
where DeptId=3
---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department
---- Fetch data from the AuditDepartment
Select *
from AuditDepartment
|
After
Delete Trigger: Now, we will create a Trigger on the
Department table which will be called after
delete the existing record from Department table and fill these records
into AuditDepartment table with the magic table Deleted as shown below:
---- create trigger to fire after delete command
executes successfully
CREATE Trigger
tri_afterdelete_department On Department
FOR Update
AS
Begin
---- Insert Records from Inserted table into
AuditDepartment
Insert into
AuditDepartment (DeptId, DeptName, DeptBudget, AuditAction, AuditDate)
Select D.DeptId,
D.DeptName,
D.DeptBudget,
AuditAction='Record Deleted Successfully- Calling After Delete
Trigger',
AuditDate =Getdate()
From Deleted
D
Print 'Trigger
has been fired successfully after delete the existing record!'
End
---- delete record from the Department table
Delete Department
where DeptId=2
---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department
---- Fetch data from the AuditDepartment
Select *
from AuditDepartment
|
Click on Triggers , difference between Triggers and Stored Procedures , Benefits of Triggers, Disadvantages of Triggers, SQL- DDL Triggers to know more.
No comments:
Post a Comment