Friday, July 24, 2015

SQL - DML Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. 
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

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00

---- 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

DeptId
DeptName
DeptBudget
AuditAction
AuditDate






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 new records into Department
Insert into Department (DeptName, DeptBudget) Values
('Admin', '99999.00')

---- Fetch data from the Department
Select DeptId,
DeptName,
DeptBudget from Department

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00
3
Sales-Marketing
95200000.00

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

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00
3
Sales-Marketing
95200000.00

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate
3
Sales-Marketing
95200000.00
Record Inserted Successfully- Calling After Insert Trigger
2015-07-24 15:00:09.150

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

DeptId
DeptName
DeptBudget
1
IT
1200000.00
2
Finance
40020000.00
3
Sales-Marketing
145000000.00

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate
3
Sales-Marketing
95200000.00
Record Inserted Successfully- Calling After Insert Trigger
2015-07-24 15:00:09.150
3
Sales-Marketing
145000000.00
Record Updated Successfully- Calling After Update Trigger
2015-07-24 15:11:37.630

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

DeptId
DeptName
DeptBudget
1
IT
1200000.00
3
Sales-Marketing
145000000.00

---- Fetch data from the  AuditDepartment
Select * from AuditDepartment

DeptId
DeptName
DeptBudget
AuditAction
AuditDate
3
Sales-Marketing
95200000.00
Record Inserted Successfully- Calling After Insert Trigger
2015-07-24 15:00:09.150
3
Sales-Marketing
145000000.00
Record Updated Successfully- Calling After Update Trigger
2015-07-24 15:11:37.630
2
Finance
40020000.00
Record Deleted Successfully- Calling After Delete Trigger
2015-07-24 15:19:32.420

No comments:

Post a Comment