How to Update a Bridge Table Automatically

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