SQL Server: Real time usage of database trigger | SQL Server Update Trigger Sample

Here we will learn the real-time need and practical usage of triggers by following example,

--Step1 – Create Training Batch Table called tabBatch

create table tabBatch

(

BatchCode char(10) primary key,

BatchInfo varchar(30) not null,

TrainerID int

)

insert into tabBatch(BatchCode,BatchInfo) 

values ('CHN19DN017','.NET Web Development')

insert into tabBatch(BatchCode,BatchInfo) 

values ('CHN19FJ018','Full stack java development')

insert into tabBatch(BatchCode,BatchInfo) 

values ('CHN19DW019','Data warehouse & Reporting')

insert into tabBatch(BatchCode,BatchInfo) 

values('CHN19ST020','Selenium Testing with Java')

Execute the above commands and check the table contents

select * from tabBatch

CHN19DN017      .NET Web Development             NULL

CHN19DW019      Data warehouse & Reporting       NULL

CHN19ST020      Selenium Testing with Java       NULL

CHN19FJ018      Full stack java development      NULL

Step2 – Create Trainers Table called tabTrainers

create table tabTrainers

(

TrainerID int primary key identity (1000,1),

TrainerName char(20) not null,

Technology varchar(20) not null,

BatchCode char(10) references tabBatch(BatchCode)

)

insert into tabTrainers(TrainerName,Technology) 

values('Syed Nawas','.NET FSD')

insert into tabTrainers(TrainerName,Technology) 

values('Sravya','Testing')

insert into tabTrainers(TrainerName,Technology) 

values('Ram Pranav','Java FSD')

insert into tabTrainers(TrainerName,Technology) 

values('Partha Saradhi','Data Warehouse')

Execute the above commands and check the table contents

1000 Syed Nawas            .NET FSD        NULL

1001 Sravya                Testing         NULL

1002 Ram Pranav            Java FSD        NULL

1003 Partha                Data Warehouse  NULL

So, new batches were created and trainers are included.

Now we need to allocate trainers to each batch by the following update commands.

--Assign Trainer to batch

update tabBatch set TrainerID=1000 where BatchCode='CHN19DN017'

--Assign Batch to Trainer

update tabTrainers set BatchCode='CHN19DN017' where TrainerID=1000

Execute the above update commands and check the results

select * from tabBatch

CHN19DN017      .NET Web Development             1000

CHN19DW019      Data warehouse & Reporting       NULL

CHN19ST020      Selenium Testing with Java       NULL

CHN19FJ018      Full stack java development      NULL

select * from tabTrainers

1000 Syed Nawas            .NET FSD        CHN19DN017

1001 Sravya                Testing         NULL

1002 Ram Pranav            Java FSD        NULL

1003 Partha                Data Warehouse  NULL

So whenever we update Batch Table (tabBatch), I need to update Trainers Table (tabTrainers) as above. Instead of executing the above 2 SQL commands for all the records in Batch Table (tabBatch), we can leave the responsibility of updating trainer table (tabTrainers) to some trigger.

Step3 – Create Update Tigger as follows and execute (Select code + F5)

create trigger AfterAssignBatchToTrainer

on tabBatch

after update

as

DECLARE @BatchCode char(10),@TrainerID int;

SELECT @BatchCode = tabBatch.BatchCode FROM INSERTED tabBatch;

SELECT @TrainerID = tabBatch.TrainerID FROM INSERTED tabBatch;

update tabTrainers set BatchCode=@BatchCode where                                                                  TrainerID=@TrainerID

PRINT 'Trigger successfully updated batch code in trainers table.'

Go

--Message: Commands completed successfully.

         Now, we have successfully created the trigger that will automatically update the Trainers table (tabTrainers) whenever any update is occurring in Batch table (tabBatch).

Just try now,

update tabBatch set TrainerID=1001 where BatchCode='CHN19ST020'

update tabBatch set TrainerID=1002 where BatchCode='CHN19FJ018'

update tabBatch set TrainerID=1003 where BatchCode='CHN19DW019'

We can get the following messages. One set of message for update happened in Trainers table(tabTrainers) and another for update happened in Batch Table (tabBatch)

(1 row affected)

Trigger successfully updated batch code in trainers table.

(1 row affected)

(1 row affected)

Trigger successfully updated batch code in trainers table.

(1 row affected)

(1 row affected)

Trigger successfully updated batch code in trainers table.

(1 row affected)

Check the record in both table

select * from tabBatch

CHN19DN017      .NET Web Development            1000

CHN19DW019      Data warehouse & Reporting      1003

CHN19FJ018      Full stack java development     1002

CHN19ST020      Selenium Testing with Java      1001

select * from tabTrainers

1000 Syed Nawas       .NET FSD        CHN19DN017

1001 Sravya           Testing         CHN19ST020

1002 Ram Pranav       Java FSD        CHN19FJ018

1003 Partha           Data Warehouse  CHN19DW019

 Hope you enjoy this sample. Thanks