Triggers In SQL Server
In this article we are going learn trigger in SQL. Let’s get started:
Basically, trigger is procedure code which is automatically executed in the response of any event occurs on the table. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion.
In SQL Server, there are 4 types of trigger:
- DML Trigger
- DDL Trigger
- CLR Triggers
- Logon Trigger
Triggers are classified into two main types.
- After triggers
- Instead of triggers
In this article we will learn DML (Insert, Delete and Update) Trigger, remaining three and instead of triggers will be in next articles.
I already have a table of Hackerfriend. You can make your own table.
Let’s create another table:
create table Hackerfriend_audit_table
(
userid nvarchar(20),
Triggermessage nvarchar(200)
)
Now let’s create trigger, Look carefully on syntax:
create trigger Hackerfriend_trigger
on Hackerfriend
after UPDATE, INSERT, DELETE
as
declare @userid nvarchar(20),@Triggermessage varchar(200),@username varchar(20),@date nvarchar(20);
if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
SET @userid = (select userid from inserted );
set @username = (select username from inserted);
set @date = getdate();
SET @Triggermessage = @username + ' Update the table Hackerfriend on ' + @date;
INSERT into Hackerfriend_audit_table(userid,Triggermessage) values(@userid,@Triggermessage);
end
If exists (Select * from inserted) and not exists(Select * from deleted)
begin
SET @userid = (select userid from inserted );
set @username = (select username from inserted);
set @date = getdate();
SET @Triggermessage = @username + ' Insert in the table Hackerfriend on ' + @date;
INSERT into Hackerfriend_audit_table(userid,Triggermessage) values(@userid,@Triggermessage);
end
If exists(select * from deleted) and not exists(Select * from inserted)
begin
SET @userid = (select userid from deleted );
set @username = (select username from deleted);
set @date = getdate();
SET @Triggermessage = @username + ' Delete from the table Hackerfriend on ' + @date;
INSERT into Hackerfriend_audit_table(userid,Triggermessage) values(@userid,@Triggermessage);
end
Please try to understand the concept rather than Logic. You can make your own logic later on.
If you check your database then you will Hackerfriend Trigger:
Let’s test the triggers:
- First update any row:
update Hackerfriend set email_ID = 'a+miri@lovebirds.com' where userid = 'abhya4554'
2 rows affected. One for Hackerfriend table other one for Hackerfriend_audit_table.
Let’s check records in both tables:
Hackerfirend Table:
Look at the lovebirds!!!
Hackerfriend_audit_table:
-
Insert in the table:
insert into Hackerfriend values('Kejri62','Kejriwal','AAP@delhi.com','Politics')
-
Delete any row from the table:
delete from Hackerfriend where userid = 'rajan1234'
you will find below records:
userid Triggermessage
rajan1234 Rajan Kumar Delete from the table Hackerfriend on Feb 26 2020 11:49PM
