Share via

How to Update a Bridge Table Automatically

  • Article

Case:

We have three tables:

  1. Department
  2. Stage
  3. And a bridge table ' Dep_Stage

There is a need to automatically add new record to bridge table when ever a new record is added to Department.  Department and Stage both can join based on the OfficeID.

Department

DepID int Primary and Identity
DepName varchar(100) Checked
OfficeID int Checked

Stage

StageID int , PK and Identity
StageName varchar(50)
OfficeID int

DEP_Stage

DSID int Primary and Identity
DepID int  
OfficeID int  
StageID int  
DSNAME varchar(100)  

Solution:

Write a trigger in the Department table like this:

CREATE 

TRIGGER [dbo].[OnInsertNewRecord] ON [dbo].[Department]  
AFTER INSERT  AS
BEGIN
    SET NOCOUNT ON; 
    Declare @DepID as int 
       Declare @Office as Int 
    Declare @DSName as varchar(50) 
    select top 1  @DepID  = DepID, @DSNAme = DepName, @Office = OfficeID  from Inserted

 -- Insert statements for trigger here

     insert into Dep_Stage (DepID, StageID, OfficeID, DSName)  
    select @DepID, Stage.StageID, @Office, @DSName + '_' + StageName From Stage  where OfficeID =@Office 
 END

 

 

GO

Similarly we can add a trigger to the stage table as well. This will automatically fill the bridge table as and when the new record is added either to the Department or to the Stage table.

This can save time in maintaining the bridge or link tables in relational data models.

If you have any questions, please ask at sushil@grokinnovations.co.uk

Thanks,

Sushil Saini
www.grokinnovations.co.uk
Grok Innovations UK ltd.
Microsoft BI Stack Experts