What is Trigger
Trigger is database object that call or run automatically
even any action like insert, update, delete is performed into table. Each table
has its own trigger that is associated with the table.
Type of Trigger:- there is two type of trigger
- After trigger
- Instead of trigger
Now, i am going both type of trigger in details with
examples
1:- After Trigger
Whenever any action like insert, update or delete is perform
on table after that these triggers are executed or we can say that, this
Trigger executed after an action such as Insert, Update or Delete is performed
on table.
I am creating Employee table where i will perform actions (like Insert, delete, update) and creating EmployeeActionLogs table to logs the trigger on the basis of action.
Following are the code to create the Employee table
CREATE TABLE [dbo].[Employee]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](100) NULL, [Age] [int] NULL, [Address] [nvarchar](100) NULL, [Department] [varchar](500) NULL, [Gender] [varchar](20) NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Following are the code to create the EmployeeActionLogs table
CREATE TABLE [dbo].[EmployeeActionLogs]( [Id] [int] IDENTITY(1,1) NOT NULL, [Action] [varchar](50) NULL, [Message] [varchar](500) NULL, CONSTRAINT [PK_EmployeeActionLogs] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
After INSERT Trigger
Following is an example of an After Insert Trigger. Whenever a row is inserted in the Employee Table, the following trigger will be executed and trigger will logs in EmployeeActoinLos table
Create TRIGGER [dbo].[Employee_INSERT] ON [dbo].[Employee] AFTER INSERT AS BEGIN SET NOCOUNT ON; DECLARE @EmployeeId INT SELECT @EmployeeId = INSERTED.Id FROM INSERTED INSERT INTO [dbo].[EmployeeActionLogs] VALUES('Insert', 'New Employee - EmployeeId is '+CAST(@EmployeeId as varchar(10))+' Inserted') END
Below code that inserts a row in Employee table.
INSERT INTO [dbo].[Employee] ([Name] ,[Age] ,[Address] ,[Department] ,[Gender]) VALUES ( 'John Joes' ,30 ,'England' ,'IT' ,'Male' ) GO
Insert action performed on employee table the associated After INSERT trigger fired and maintained the EmployeeActionLogs table that is below
Id Action Message 2 Insert New Employee - EmployeeId is 19 Inserted 9 Insert New Employee - EmployeeId is 26 Inserted
After UPDATE Trigger
Following is an example of an After UPDATE Trigger. Whenever
a row is Updated in the Employee Table,
the following trigger will be executed and trigger will logs in
EmployeeActoinLos table.
CREATE TRIGGER [dbo].[Employee_UPDATE] ON [dbo].[Employee] AFTER UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @EmployeeId INT DECLARE @EmployeeName INT SELECT @EmployeeId = INSERTED.Id,@EmployeeName=INSERTED.Name FROM INSERTED INSERT INTO [dbo].[EmployeeActionLogs] VALUES('Update', 'Existing EmployeeId '+CAST(@EmployeeId as varchar(10))+' is updated with name - '+@EmployeeName) END
Below code that Update a row in Employee table.
USE [SQLCLR] GO DECLARE @EmployeeId INT=19 UPDATE [dbo].[Employee] SET [Name] = 'New John Joes' WHERE Id=@EmployeeId GO
UPDATE action performed on employee table the associated
After UPDATE trigger fired and
maintained the EmployeeActionLogs table that is below.
Id Action Message 2 Insert New Employee - EmployeeId is 19 Inserted 9 Insert New Employee - EmployeeId is 26 Inserted 10 Update Existing EmployeeId 19 is updated with name - New John Joes
After DELETE Trigger
2:- Instead of trigger
Following is an example of an After DELETE Trigger. Whenever
a row is Deleted from the Employee Table, the following trigger will be
executed and trigger will logs in EmployeeActoinLos table.
Create TRIGGER [dbo].[Employee_DELETE] ON [dbo].[Employee] AFTER DELETE AS BEGIN SET NOCOUNT ON; DECLARE @EmployeeId INT SELECT @EmployeeId = INSERTED.Id FROM INSERTED INSERT INTO [dbo].[EmployeeActionLogs] VALUES('Delete', 'Existing EmployeeId '+CAST(@EmployeeId as varchar(10))+' is Deleted') END
Below code that Delete a row in Employee table.
DECLARE @EmployeeId INT=19 DELETE FROM [dbo].[Employee] WHERE id=@EmployeeId GO
DELETE action performed on employee table the associated
After DELETE trigger fired and
maintained the EmployeeActionLogs table that is below.
Id Action Message 2 Insert New Employee - EmployeeId is 19 Inserted 9 Insert New Employee - EmployeeId is 26 Inserted 10 Update Existing EmployeeId 19 is updated with name - New John Joes 11 Delete Existing EmployeeId 19 is Deleted 12 Delete Existing EmployeeId 26 is Deleted
2:- Instead of trigger
Instead of trigger executed before the action (like insert,
update or delete) is performed. It is fully different from after trigger.
suppose you want to insert record in a table then the trigger that are
associated with the insert action on the table will fire before the row follow
all the checks, like key, rules, and constraints. If it is fails to insert
record, SQL Server will fire the Instead of Trigger that associated with table.
Following is an example of an INSTEAD OF DELETE. this trigger will fire before a row is Deleted from the Employee Table, and logs in EmployeeActoinLos table
Following is an example of an INSTEAD OF DELETE. this trigger will fire before a row is Deleted from the Employee Table, and logs in EmployeeActoinLos table
Create TRIGGER [dbo].[Employee_InsteadOFDelete] ON [dbo].[Employee] INSTEAD OF DELETE AS BEGIN SET NOCOUNT ON; DECLARE @EmployeeId INT SELECT @EmployeeId = DELETED.Id FROM DELETED IF(@EmployeeId=1) BEGIN RAISERROR('admin cannot be deleted',16 ,1) ROLLBACK INSERT INTO EmployeeActionLogs VALUES('INSTEADOFDELETE', 'admin cannot be deleted.') END ELSE BEGIN INSERT INTO [dbo].[EmployeeActionLogs] VALUES('Delete', 'Existing Employee is Deleted') END END
Below code that Delete a row in Employee table.
Logs in EmployeeActoinLos table
Id Action Message
2 Insert New Employee - EmployeeId is 19 Inserted
9 Insert New Employee - EmployeeId is 26 Inserted
10 Update Existing EmployeeId 19 is updated with name - New John Joes
11 Delete Existing EmployeeId 19 is Deleted
12 Delete Existing EmployeeId 26 is Deleted
13 INSTEADOFDELETE admin cannot be deleted.
No comments:
Post a Comment