Sql triggers tutorial in hindi

What is a Trigger :
A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically.
Types Of Triggers
1. After Triggers (For Triggers) : After Insert, After Update, After Delete
2. Instead Of Triggers
CREATE TABLE tblemployee(Emp_ID INT Identity,Emp_name Varchar(100),Emp_Sal Decimal (10,2))
INSERT INTO tblemployee VALUES ('Anand',15000);
INSERT INTO tblemployee VALUES ('Neha',12400);
INSERT INTO tblemployee VALUES ('Sohan',11500);
INSERT INTO tblemployee VALUES ('Vijay',15500);
INSERT INTO tblemployee VALUES ('Sapna',14000);
I will be creating an AFTER INSERT TRIGGER which will insert the rows inserted into the table into another log table. The main purpose of this log table is to record the changes in the main table.
Now, create the log table as:-
CREATE TABLE Employee_Log
(Emp_ID int,Emp_name varchar(100),Emp_Sal decimal (10,2),Log_Action varchar(100),log_Timestamp datetime)
(a) After Insert Trigger
This trigger is fired after an INSERT on the table. Let’s create the trigger as:
CREATE TRIGGER trgAfterInsert ON tblemployee
After INSERT
AS
declare @empid int
declare @empname varchar(100)
declare @empsal decimal(10,2)
declare @log_action varchar(100)
select @empid=i.Emp_ID, @empname=i.Emp_Name, @empsal=i.Emp_Sal from inserted i;
set @log_action='Inserted Record -- After Insert Trigger.';
insert into Employee_log
(Emp_ID,Emp_Name,Emp_Sal,Log_Action,Log_Timestamp)
values(@empid,@empname,@empsal,@log_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
The CREATE TRIGGER statement is used to create the trigger. THE ON clause specifies the table name on which the trigger is to be attached. The after INSERT specifies that this is an AFTER
INSERT trigger. In the trigger body, table named inserted has been used. This table is a logical table and contains the row that has been inserted. I have selected the fields from the logical inserted table from the row that has been inserted into different variables, and finally inserted those values into the log table.
To see the newly created trigger in action, lets insert a row into the main table as:
insert into tblemployee values('Ram',10500);
Now, a record has been inserted into the tblemployee table. The AFTER INSERT trigger attached tothis table has inserted the record into the Employee_Log as:
6 Ram 10500.00 Inserted Record -- After Insert Trigger. 2016-10-10 08:28:00.500
(b) AFTER UPDATE Trigger
This trigger is fired after an update on the table. Let’s create the trigger as:
CREATE TRIGGER trgAfterUpdate ON tblemployee
After UPDATE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @log_action varchar(100);
select @empid=i.Emp_ID, select @empname=i.Emp_Name, select @empsal=i.Emp_Sal from inserted i;
if update(Emp_Name)
set @alog_action='Updated Record -- After Update Trigger.';
if update(Emp_Sal)
set @log_action='Updated Record -- After Update Trigger.';
insert into Employee_Log(Emp_ID,Emp_Name,Emp_Sal,Log_Action,Log_Timestamp)
values(@empid,@empname,@empsal,@log_action,getdate());
PRINT 'AFTER UPDATE Trigger fired.'
GO
The AFTER UPDATE Trigger is created in which the updated record is inserted into the log table.
There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from theupdate(column_name) function. In our trigger, we have used, if update(Emp_Name) to check if the column Emp_Name has been updated. We have similarly
checked the column Emp_Sal for an update.
Let’s update a record column and see what happens.
update tblemployee set Emp_Sal=15000 where Emp_ID=6
This inserts the row into the audit table as:
6 Ram 15000.00 Updated Record -- After Update Trigger. 2016-10-10 08:28:00.500
(c) AFTER DELETE Trigger
This trigger is fired after a delete on the table. Let’s create the trigger as:
CREATE TRIGGER trgAfterDelete ON tblemployee
AFTER DELETE
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @log_action varchar(100);
select @empid=d.Emp_ID, @empname=d.Emp_Name, select @empsal=d.Emp_Sal from deleted d;
set @log_action='Deleted -- After Delete Trigger.';
insert into Employee_log
(Emp_ID,Emp_Name,Emp_Sal,Log_Action,Log_Timestamp)
values(@empid,@empname,@empsal,@Log_action,getdate());
PRINT 'AFTER DELETE TRIGGER fired.'
GO
In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the Log table. Let’s fire a delete on the main table. A record has been inserted into the audit table as:
6 Ram 15000.00 Deleted -- After Delete Trigger. 2016-10-10 08:28:00.500
www.libitsoluti...

[@lekhasuresh5909]

thanku soo much ............ ur my life saver

[@shivkumarkanojiya8050]

hello sir what RAISERROR('Cannot delete where salary > 1200',16,1);
ROLLBACK;
16,1 kya hai

[@mohitverma2033]

you must be from m.p sir ji. please rpl if u frm there.

[@ajitsinha6801]

Ek baar procedure, package achhe se ek video bana kar shamjha dijiye sir please 🙏🙏🙏🙏

[@LearnEverythingByAnand]

Ok

[@neeraj2428]

Hello sir, thank you so much for trigger in sql video. please upload the video for Events in mysql in HIndi

[@laharitumbalam3054]

Thank you so much sir... well explained !

[@insane_billa]

Truncate hua h drop nahi. Dubara create nahi hoga.

[@shilpigupta8228]

Hello sir, can you make vedio on DCL command in detail

[@LearnEverythingByAnand]

k i will provide u

[@deepakalinavlogs]

final video to understand trigger in sql . thank you

[@manojkumawat1491]

Best explanation with example👌👌👍👍

[@shubhangisambhare8154]

Ohh thanks sir very nice lectures 😇😇😇

[@nehamarmat5962]

how can i create getDate() function

[@ashitoshthakur9402]

kya sir itna speed, well thank you lot

[@sudheergupta397]

can you explain same for mysql ? plz

[@mohdabdul6662]

thanks bhai .its helpful video

[@gurpreetsingh8918]

hii sir,
can we create a single trigger which affects more than 1 table.
For eg:
I have 5 tables, and i update table and all other tables gets updated automatically.

[@LearnEverythingByAnand]

+gurpreet singh yes

[@gurpreetsingh8918]

Learn Everything may i know how?

[@LearnEverythingByAnand]

+gurpreet singh from inserted table we can set values in variable and save to n number of tables

[@gurpreetsingh8918]

Learn Everything
am sorry sir, i dont get it.
can u give me sample code

[@rajalaxmibehera1336]

can you send me thisr code

[@er.manojsingh7359]

Nice

[@atozonlineguide9893]

Good to learn, keep it up 👍👌

[@LearnEverythingByAnand]

thank you for your response

[@LogicalManSujeet]

zoom kr k dikhaiye

[@PrinceKumar-tp9tz]

Very well explained

[@adeelahmed8159]

sahi hai

[@SSDivineschool]

Thanks

[@arpanasingh667]

good for beginners

[@LearnEverythingByAnand]

thank you for your response,

[@shehbazsheikh9068]

NICE

[@saratchand4275]

good

[@muhammadmudassirali27]

Sir kindly send the code

[@LearnEverythingByAnand]

+Mudasar Ali code of which video

[@AkhilBawaakhilbawa]

Great Explanation sir

[@LearnEverythingByAnand]

+Akhil Bawa thanks