Monday, September 06, 2004

Trigger instead of relational constraint in MS SQL

Recently during a project I faced with a problem in DB designing, I had to create a relation between more than three tables in various levels as follows

Table 1:

Id - Primary key
Name - Unique

Table 2:
Id - Primary key
Table1_Name - References Table1(Name)

Table 3:
Id - Primary key
Table1_Name - References Table1(Name)
Table2_Name - References Table2(Name)

Well, the above is a very breif representation of the architecture used in the system for the sake of clarity.

So once this setup is done, sql wouldnt allow to have update / delete on cascade for the Table1_Name column on Table3.

One way to work around this problem, which I am using now, is to remove the foreign key reference from Table3 and add a "For Update" and "For Delete" trigger on table 1 which will take care of the updation and deletion work on Table3.

Disadvantage to this approach will be that I will have to remember to update the trigger if in case the strucuture of Table3 changes. But then I will be removing this kind of design on the next architecture revision of the system, till then this can be used.

The Trigger:

Create Trigger Table1_Update On Table1
For Update As
Update Table3 Set Table3.Table1_Name=inserted.[Name]
From Table3, inserted, deleted
Where Table3.Table1_Name=deleted.[Name]


Post a Comment

<< Home