A trigger is SQL Code that automatically executes when an event occurs in the database server.
DML Trigger
DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.
DML events are INSERT, UPDATE, or DELETE statements on a table or view.
DML events are INSERT, UPDATE, or DELETE statements on a table or view.
DDL Trigger
DDL triggers execute in response to a variety of data definition language (DDL) events. DDL Events are CREATE, ALTER, and DROP statements.
Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.
DML Trigger
2 types
1) for trigger / After trigger
2) Instead of trigger
Syntax:
Create Trigger Trg_Name ON TableName For INSERT
AS
BEGIN
--Code
END
Some Points about DML Trigger
1) We Can't create trigger on a temporary object. (temporary table)
2) As we know Triggers are fired/called whenever a modification is made to a table,
thus slow down the performance of DML statements.
3) truncate table statement - which does not activate trigger.
4) Enable / Disable Trigger
--------------Disable Trigger
Disable Trigger Trg_Insert_INOF ON Customer
--------------Enable Trigger
Enable Trigger Trg_Insert_INOF ON Customer
Magic Table in SQL Server
Inserted & Deleted are 2 magic tables in sql server
1) for trigger / After trigger
If you insert record/row in a table then the trigger associated with the insert event on this table will execute 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 execute the After Trigger.
or we can say Post DML operation (insert / Update / Delete) this trigger will execute.
CREATE TABLE [dbo].[Customer]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Salary] [decimal](7, 2) NULL
)
CREATE TABLE [dbo].[Audit]
(
[CustID] [int] NOT NULL,
[OldSalary] [decimal](7, 2) NULL,
[NewSalary] [decimal](7, 2) NULL,
ISDeleted bit NOT NULL Default 0,
UserName [varchar](50) NULL,
ActionDateTime DateTime Default getdate()
)
Select * from [dbo].[Customer]
Select * from [dbo].[Audit]
INSERT [dbo].[Customer] ([Name], [Salary]) VALUES (N'Sagar', 25222.35 )
INSERT [dbo].[Customer] ([Name], [Salary]) VALUES (N'Ajit',12015.54 )
INSERT [dbo].[Customer] ([Name], [Salary]) VALUES (N'Nilesh',58445.54 )
-------------------------------Insert----------------------------------
Create Trigger Trg_insert ON [Customer] For INSERT
AS
Begin
Declare @Oldsal [decimal](7, 2)
Declare @Newsal [decimal](7, 2)
Declare @CustID INT
Set @CustID=(Select ID From inserted)
Set @Newsal=(Select Salary From inserted)
Insert Into Audit(CustID,OldSalary,NewSalary,UserName) Values(@CustID,@Oldsal,@Newsal,SUSER_NAME())
End
INSERT [dbo].[Customer] ([Name], [Salary]) VALUES ('Atul',14000.52)
INSERT [dbo].[Customer] ([Name], [Salary]) VALUES ('Soham',90008.82)
Select * from [Customer]
Select * from Audit
-------------------------------Update---------------------------------
Create Trigger trg_Update On [Customer] For Update
As
Begin
Declare @Oldsal [decimal](7, 2)
Declare @Newsal [decimal](7, 2)
Declare @CustID INT
Set @CustID=(Select ID From inserted)
Set @Oldsal=(Select Salary From deleted)
Set @Newsal=(Select Salary From inserted)
Insert Into Audit(CustID,OldSalary,NewSalary,UserName) Values(@CustID,@Oldsal,@Newsal,SUSER_NAME())
End
Update [Customer] Set Salary=25000 Where Id=5
Select * from [dbo].[Customer]
Select * from [dbo].[Audit]
-------------------------------Delete----------------------------------
Create Trigger Trig_Delete On [Customer] For Delete
As
Begin
Declare @Oldsal [decimal](7, 2)
Declare @Newsal [decimal](7, 2)
Declare @CustID INT
Set @CustID=(Select ID From deleted)
Insert Into Audit(CustID,ISDeleted,UserName) Values(@CustID,1,SUSER_NAME())
End
Delete From [Customer] Where Id=5
--------------------------Combined---------------------------------
Create Trigger trg_DML On Customer For INSERT, Update
As
Begin
Declare @Oldsal [decimal](7, 2)
Declare @Newsal [decimal](7, 2)
Declare @CustID INT
Set @CustID=(Select ID From inserted)
Set @Oldsal=(Select Salary From deleted)
Set @Newsal=(Select Salary From inserted)
Insert Into Audit(CustID,OldSalary,NewSalary,UserName) Values(@CustID,@Oldsal,@Newsal,SUSER_NAME())
exec Sp_execfromtrg --call SP in trigger
End
Go
Create Procedure Sp_execfromtrg
AS
begin
Print 'SP CALL FROM TRIGGER'
end
--bulk Operation
Update [Customer] Set Salary=25000
Delete from Customer
--we will get below message
--Msg 512, Level 16, State 1, Procedure trg_DML , Line 7 [Batch Start Line 56]
--Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
we have to write custom code to handle multiple records, then we can handle multiple records in one go.
2) Instead of trigger
If you insert record/row in a table
then the trigger associated with the insert/Update/delete event on this table
will execute before the row passes all the checks, such as primary key, rules, and constraints.
If the record/row insertion fails, SQL Server will execute the Instead of Trigger.
or we can say Pre DML operation (insert / Update / Delete) this trigger will execute.
Drop Table IF Exists [dbo].[Customer]
Drop Table IF Exists [dbo].[Sales]
GO
CREATE TABLE [dbo].[Customer]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL
)
GO
CREATE TABLE [dbo].[Sales]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NULL,
Amount Decimal(10,2)
)
GO
INSERT [dbo].[Customer] ([Name]) VALUES (N'Sagar')
INSERT [dbo].[Customer] ([Name]) VALUES (N'Ajit')
INSERT [dbo].[Customer] ([Name]) VALUES (N'Nilesh')
GO
INSERT [dbo].[Sales](CustomerId,Amount) VALUES (1,455.5)
INSERT [dbo].[Sales](CustomerId,Amount) VALUES (2,548.52)
INSERT [dbo].[Sales](CustomerId,Amount) VALUES (3,244.3)
GO
-----------------Instead of Insert Trigger----------------------------
before Insert data into Sales Table we have to validate that customer should be available in customer table first then we can allow app-user to insert data.
Create Trigger Trg_InstdInsert
ON Sales
Instead Of Insert
As
Begin
Declare @custid Int
Declare @Amount Decimal(10, 2)
Set @custid = ( Select CustomerId from Inserted)
Set @Amount = (Select Amount from Inserted)
If Exists (Select id from [Customer] where id = @custid)
begin
INSERT [dbo].[Sales](CustomerId,Amount) VALUES (@custid,@Amount)
end
Else
BEGIN
Select 'Customer id is not present in Customer table'
END
End
example:
execute below query, to check,
INSERT [dbo].[Sales](CustomerId,Amount) VALUES (4,85.3)
-----------------Instead of Delete Trigger----------------------------
before delete data into Customer Table we have to validate that customer records
are available in Sales table (child tables), if yes then message to user, else delete customer.
Create Trigger Trg_InstdDelete ON [Customer] Instead Of Delete
As
Begin
Declare @id int
Set @id=(Select id from deleted)
If Exists(Select CustomerId from Sales where CustomerId=@id)
BEgin
Print 'In Sales Table Records available for customer id='+Convert(varchar(5),@id)
end
Else
begin
Delete From [Customer] where Id=@id
end
End
example:
execute below query, to check,
-----------------Instead of Update Trigger----------------------------
Drop Table If exists Employee
Drop Table If exists Department
Create table Department
(
DepartmentId int identity(1,1) Primary Key,
Name varchar(50) Not Null
)
GO
Create table Employee
(
Id int identity(1,1) Primary Key,
FirstName varchar(50) Not Null,
LastName varchar(50) Not Null,
City varchar(50) Not Null,
AadharcardNumber BIGINT Not Null,
DepartmentId int null Foreign Key references Department(DepartmentId)
)
Insert Into Department(Name) values ('Account')
Insert Into Department(Name) values ('IT')
Insert Into Department(Name) values ('Finance')
Insert Into Department(Name) values ('Personnel')
Insert Into Department(Name) values ('Admin')
Insert Into Employee (FirstName,LastName,City,AadharcardNumber,DepartmentId)
values
('Manish','Sonawane','Pune',121232565545,1),
('Ganesh','Khedkar','Sangli',121212125559,2)
create View VW_empDept
AS
Select
E.id,
E.FirstName,
E.LastName,
E.city,
E.AadharcardNumber,
D.Name As DepartmentName
from Employee E left join Department D
on E.DepartmentId=D.DepartmentId
Go
create View VW_emp
AS
Select E.Id,
E.FirstName,
E.LastName
from Employee E
Go
Select * from VW_emp
Select * from VW_empDept
Select * from Department
Select * from Employee
if we have to update view - for single table we can but for multiple table in one update statement it's not possible,
update VW_empDept Set FirstName='Testuser',DepartmentName='Account1' where id=1
--Msg 4405, Level 16, State 1, Line 183
--View or function 'VW_empDept' is not updatable because the modification affects multiple base tables.
Solution for this - Instead Of Update trigger create on view.
Create Trigger Trg_Instdupdate ON VW_empDept Instead Of Update
As
Begin
Declare @FirstName varchar(50)
Declare @Departmentname varchar(50)
Declare @EmpId INT
Set @EmpId =(Select Id from Inserted)
Set @FirstName =(Select FirstName from Inserted)
Set @Departmentname =(Select DepartmentName from Inserted)
---Employee
Update VW_empDept Set FirstName=@FirstName where Id=@EmpId
--Dept
Update VW_empDept Set DepartmentName=@Departmentname where Id=@EmpId
End
Comments
Post a Comment
If you have any doubt then please let me know in comment section.