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 INSERT, UPDATE, or DELETE statements on a table or view.
Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:
Triggers could be defined on the table, view, schema, or database with which the event is associated.
Advantages of using SQL triggers:-
Triggers can be written for the following purposes:
Types of DML Triggers:-
1. After Trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example :If you insert record/ or 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/ or row insertion fails, SQL Server will not fire the After Trigger.
2. Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action that fired it. This is totaly different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.
Example :If you insert record/ or row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record or row insertion fails, SQL Server will fire the Instead of Trigger.
Here first I am going to create two tables Product and Audit_Product. Product tables is main table for product and before and after DML operations get executed on this table every product details will be auditing in Audit_Product table using sql triggers.
1.Create first table [tblProduct]
2. Create second table [tblAudit_Product]
Insert some records in Product table :-
1. After Trigger
Now Here I will explain the use of After/For Trigger using Insert, Update, Delete statement with example
Output will be:-
Now Select record from both tables
After insertion in Product table the same product details inserted in Audit_Product table with Audit Action 'Inserted Record -- After Insert Trigger'.
1.b After Update Trigger
Creating Trigger:-
Now Select record from both tables
After Updating Price in Product table the same product details inserted in Audit_Product table with Audit action 'Update Record -- After Update Trigger'.
1.c After Delete Trigger
Creating Trigger:-
Now Select record from both tables
Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in response to any of the following events:
- A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
- A database definition (DDL) statement (CREATE, ALTER, or DROP).
- A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers could be defined on the table, view, schema, or database with which the event is associated.
Advantages of using SQL triggers:-
Triggers can be written for the following purposes:
- Generating some derived column values automatically
- Enforcing referential integrity
- Event logging and storing information on table access
- Auditing
- Synchronous replication of tables
- Imposing security authorizations
- Preventing invalid transactions
Types of DML Triggers:-
1. After Trigger (using FOR/AFTER CLAUSE)
This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example :If you insert record/ or 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/ or row insertion fails, SQL Server will not fire the After Trigger.
2. Instead of Trigger (using INSTEAD OF CLAUSE)
This trigger fires before SQL Server starts the execution of the action that fired it. This is totaly different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.
Example :If you insert record/ or row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record or row insertion fails, SQL Server will fire the Instead of Trigger.
Here first I am going to create two tables Product and Audit_Product. Product tables is main table for product and before and after DML operations get executed on this table every product details will be auditing in Audit_Product table using sql triggers.
1.Create first table [tblProduct]
CREATE TABLE [dbo].[tblProduct](
[fldSno] [int] IDENTITY(1,1) NOT NULL,
[fldProductId] [int] NULL,
[fldProductName] [varchar](50) NULL,
[fldProductPrice] [numeric](18, 2) NULL,
[fldSysInsertedDate] [datetime]
NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tblProduct] ADD CONSTRAINT
[DF_tblProduct_fldSysInsertedDate]
DEFAULT (getdate()) FOR [fldSysInsertedDate]
GO
2. Create second table [tblAudit_Product]
CREATE TABLE [dbo].[tblAudit_Product](
[fldSno] [int] IDENTITY(1,1) NOT NULL,
[fldProductId] [int] NULL,
[fldProductName] [varchar](50) NULL,
[fldProductName] [varchar](50) NULL,
[fldProductPrice] [numeric](18, 2) NULL,
[Audit_Timestamp] [datetime]
NULL,
[fldAuditAction]
[varchar](500) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblAudit_Product] ADD CONSTRAINT
[DF_tblAudit_Product_Audit_Timestamp]
DEFAULT (getdate()) FOR [Audit_Timestamp]
GO
Insert some records in Product table :-
INSERT [dbo].[tblProduct] ([fldProductId], [fldProductName], [fldProductPrice])
VALUES (101, N'Mi 3', CAST(14000.00 AS Numeric(18, 2)))
GO
INSERT [dbo].[tblProduct] ([fldProductId], [fldProductName], [fldProductPrice])
VALUES ( 102, N'Redmi 1 S', CAST(6000.00 AS Numeric(18, 2)))
GO
Now Records in Product table are:-
1. After Trigger
Now Here I will explain the use of After/For Trigger using Insert, Update, Delete statement with example
1.a After Insert Trigger
Creatring Trigger:-
CREATE TRIGGER trgAfterInsert on [dbo].[tblProduct]
FOR INSERT
AS declare @ProId int,
@ProName varchar(55),
@ProPrice decimal(10,2),
@audit_action varchar(500);
select @ProId=i.fldProductId from inserted i;
select @ProName=i.fldProductName from inserted i;
select @ProPrice=i.fldProductPrice from inserted i;
set @audit_action='Inserted Record -- After
Insert Trigger.';
insert into [dbo].[tblAudit_Product]
(fldProductId,fldProductName, fldProductPrice,fldAuditAction,Audit_Timestamp)
values (@ProId,@ProName,@ProPrice,@audit_action,getdate());
PRINT 'AFTER
INSERT trigger fired.'
--Now try to insert data in Product table
Insert into [dbo].[tblProduct] (fldProductId,fldProductName,fldProductPrice)
values('103','Redmi Note',9000)
Output will be:-
Now Select record from both tables
Select * from [dbo].[tblProduct]
Select * from [dbo].[tblAudit_Product]
After insertion in Product table the same product details inserted in Audit_Product table with Audit Action 'Inserted Record -- After Insert Trigger'.
1.b After Update Trigger
Creating Trigger:-
Create TRIGGER [dbo].[trgAfterUpdate] on [dbo].[tblProduct]
FOR INSERT
AS declare @ProId int,
@ProName varchar(55),
@ProPrice decimal(10,2),
@audit_action varchar(500);
select @ProId=i.fldProductId from inserted i;
select @ProName=i.fldProductName from inserted i;
select @ProPrice=i.fldProductPrice from inserted i;
if update(fldProductPrice)
set @audit_action='Update Record -- After
Update Trigger.';
insert into [dbo].[tblAudit_Product]
(fldProductId,fldProductName, fldProductPrice,fldAuditAction,Audit_Timestamp)
values (@ProId,@ProName,@ProPrice,@audit_action,getdate());
PRINT 'AFTER Update trigger fired.'
Now try to update price in Product table
Update [dbo].[tblProduct] set fldProductPrice='6500' where fldProductId='102'
Output will be
Now Select record from both tables
Select * from [dbo].[tblProduct]
Select * from [dbo].[tblAudit_Product]
After Updating Price in Product table the same product details inserted in Audit_Product table with Audit action 'Update Record -- After Update Trigger'.
1.c After Delete Trigger
Creating Trigger:-
Create TRIGGER [dbo].[trgAfterDelete] on [dbo].[tblProduct]
FOR Delete
AS declare @ProId int,
@ProName varchar(55),
@ProPrice decimal(10,2),
@audit_action varchar(500);
select @ProId=i.fldProductId from deleted i;
select @ProName=i.fldProductName from deleted i;
select @ProPrice=i.fldProductPrice from deleted i;
set @audit_action='Delete Record -- After
Delete Trigger.';
insert into [dbo].[tblAudit_Product]
(fldProductId,fldProductName, fldProductPrice,fldAuditAction,Audit_Timestamp)
values (@ProId,@ProName,@ProPrice,@audit_action,getdate());
PRINT 'AFTER Delete trigger
fired.'
Now try to Delete Recordin from Product table
Delete From [dbo].[tblProduct] where fldProductId='103'
Output will be
Now Select record from both tables
Select * from [dbo].[tblProduct]
Select * from [dbo].[tblAudit_Product]
After Deleting Record from Product table, it has inserted in Audit_Product table with Audit action 'Delete Record -- After DeleteTrigger'.
2. Instead of Trigger
Now Here I will explain the use of Instead of Trigger using Insert, Update, Delete statement with example
Now I will Insert two records :-
Output of above both queries
In the above query first row got inserted and second not because of our validation logic if(@ProPrice<1000) it raised error 'Cannot Insert where Price < 1000'. and see output below.
Select data from both tables:-
Here now I will update two rcords in table [dbo].[tblProduct]:-
In first query record has updated but not in second because of our validation logic if(@ProPrice<1000) it raised error 'Cannot Update where ProductPrice < 1000'. see below output
Select data from both tables:-
There is erroer in above query because of our validation in trigger and error message is
'Cannot delete where Price > 1200'.
Noew I will insert one more record that have price less then 1200:-
Now I will delete this recently inserted rcord let see what happen:-
Now it has deleted from product table. Let me check now both tables :-
Here if you look in both tables, The record removed from product table and it has audited in Audit_Product table see last record.
In the above article I try to explain the each type of Trigger in SQL with examples. I hope its useful to you. Please post your feedback, question, or comments about this article.
After Deleting Record from Product table, it has inserted in Audit_Product table with Audit action 'Delete Record -- After DeleteTrigger'.
2. Instead of Trigger
Now Here I will explain the use of Instead of Trigger using Insert, Update, Delete statement with example
2.a Instead of Insert Trigger
Creatring Trigger:-
Create TRIGGER [dbo].[trgInsteadOfInsert] on
[dbo].[tblProduct]
Instead of INSERT
AS declare @ProId int,
@ProName varchar(55),
@ProPrice decimal(10,2),
@audit_action varchar(500);
select @ProId=i.fldProductId from inserted i;
select @ProName=i.fldProductName from inserted i;
select @ProPrice=i.fldProductPrice from inserted i;
SET @audit_action='Inserted Record -- Instead
Of Insert Trigger.';
BEGIN
BEGIN TRAN
SET NOCOUNT ON
if(@ProPrice<1000)
begin
RAISERROR('Cannot Insert where Price < 1000',16,1); ROLLBACK;
end
else
begin
insert into [dbo].[tblAudit_Product]
(fldProductId,fldProductName, fldProductPrice,fldAuditAction,Audit_Timestamp)
values (@ProId,@ProName,@ProPrice,@audit_action,getdate());
INSERT [dbo].[tblProduct] ([fldProductId], [fldProductName], [fldProductPrice])
VALUES (@ProId, @ProName, @ProPrice)
COMMIT;
PRINT 'Record Inserted --
Instead Of Insert Trigger.';
End
End
Now I will Insert two records :-
INSERT [dbo].[tblProduct] ([fldProductId], [fldProductName], [fldProductPrice])
VALUES (104, N'Mi 4g', CAST(12000.00 AS Numeric(18, 2)))
INSERT [dbo].[tblProduct] ([fldProductId], [fldProductName], [fldProductPrice])
VALUES ( 105, N'Redmi 2 S', CAST(900.00 AS Numeric(18, 2)))
Output of above both queries
In the above query first row got inserted and second not because of our validation logic if(@ProPrice<1000) it raised error 'Cannot Insert where Price < 1000'. and see output below.
Select data from both tables:-
Select * from [dbo].[tblProduct]
Select * from [dbo].[tblAudit_Product]
2.b Instead of UpdateTrigger
Creatring Trigger:-
Create TRIGGER [dbo].[trgInsteadOfUpdate] on
[dbo].[tblProduct]
Instead of update
AS declare @ProId int,
@ProName varchar(55),
@ProPrice decimal(10,2),
@audit_action varchar(500);
select @ProId=i.fldProductId from inserted i;
select @ProName=i.fldProductName from inserted i;
select @ProPrice=i.fldProductPrice from inserted i;
set @audit_action='Update Record -- After
Update Trigger.';
BEGIN
BEGIN TRAN
if(@ProPrice<1000)
begin
RAISERROR('Cannot Update where ProductPrice < 1000',16,1); ROLLBACK;
end
else
begin
insert into [dbo].[tblAudit_Product]
(fldProductId,fldProductName, fldProductPrice,fldAuditAction,Audit_Timestamp)
values (@ProId,@ProName,@ProPrice,@audit_action,getdate());
end
COMMIT;
PRINT 'Record Updated --
Instead Of Update Trigger.';
END
Here now I will update two rcords in table [dbo].[tblProduct]:-
Update [dbo].[tblProduct] set fldProductPrice='15000' where fldProductId='104'
Update [dbo].[tblProduct] set fldProductPrice='700' where fldProductId='104'
Above uqeries Output:-
In first query record has updated but not in second because of our validation logic if(@ProPrice<1000) it raised error 'Cannot Update where ProductPrice < 1000'. see below output
Select data from both tables:-
Select * from [dbo].[tblProduct]
Select * from [dbo].[tblAudit_Product]
2.c Instead of Delete Trigger
Creatring Trigger:-
Here now I will delete one rcords in table [dbo].[tblProduct]:-
CREATE TRIGGER [dbo].[trgInsteadOfDelete]
on [dbo].[tblProduct]
Instead of Delete
AS declare @ProId int,
@ProName varchar(55),
@ProPrice decimal(10,2),
@audit_action varchar(500);
select @ProId=i.fldProductId from deleted i;
select @ProName=i.fldProductName from deleted i;
select @ProPrice=i.fldProductPrice from deleted i;
set @audit_action='Delete Record -- Instead of
Delete Trigger.';
BEGIN
BEGIN TRAN
if(@ProPrice>1200)
begin
RAISERROR('Cannot delete where Price > 1200',16,1);
ROLLBACK;
end
else
begin
delete from [tblProduct] where fldProductId=@ProId;
insert into [dbo].[tblAudit_Product]
(fldProductId,fldProductName, fldProductPrice,fldAuditAction,Audit_Timestamp)
values (@ProId,@ProName,@ProPrice,@audit_action,getdate());
end
COMMIT;
PRINT 'Instead of Delete
trigger fired.';
end
There is erroer in above query because of our validation in trigger and error message is
'Cannot delete where Price > 1200'.
Noew I will insert one more record that have price less then 1200:-
INSERT [dbo].[tblProduct] ([fldProductId], [fldProductName], [fldProductPrice])
VALUES (105, N'Mi 5g', CAST(1100.00 AS Numeric(18, 2)))
See in Product table it has inserted now:-
Now I will delete this recently inserted rcord let see what happen:-
Now it has deleted from product table. Let me check now both tables :-
Select * from [dbo].[tblProduct]
Select * from [dbo].[tblAudit_Product]
Here if you look in both tables, The record removed from product table and it has audited in Audit_Product table see last record.
In the above article I try to explain the each type of Trigger in SQL with examples. I hope its useful to you. Please post your feedback, question, or comments about this article.
No comments:
Post a Comment