SQL Server - BEFORE INSERT Trigger

Question

I have two tables as follows:

Table 1 'MySkills' [ primary key (EmpId, SkillId)]

+----------+------------+---------+----------+-----------+------------+-----------------+----------------+
|  EmpId   | CategoryId | SkillId | ExpYears | ExpMonths | Experience | RatingSubmitted | RatingApproved |
+----------+------------+---------+----------+-----------+------------+-----------------+----------------+
| CSSL9610 | arcgis     | arcgis1 | 0.00     | 0.00      | 0.00       | 1.00            | NULL           |
| CSSL9610 | arcgis     | arcgis2 | 0.00     | 0.00      | 0.00       | 0.00            | NULL           |
| CSSL9610 | arcgis     | arcgis3 | 0.00     | 0.00      | 0.00       | 0.00            | NULL           |
| CSSL9610 | arcgis     | arcgis4 | 0.00     | 0.00      | 0.00       | 0.00            | NULL           |
| CSSL9610 | arcgis     | arcgis5 | 0.00     | 0.00      | 0.00       | 0.00            | NULL           |
| CSSL9610 | arcgis     | arcgis6 | 0.00     | 0.00      | 0.00       | 0.00            | NULL           |
| CSSL9610 | arcgis     | arcgis7 | 0.00     | 0.00      | 0.00       | 0.00            | NULL           |
+----------+------------+---------+----------+-----------+------------+-----------------+----------------+

And Table 1 'MySkills_details' [ primary key (EmpId)]

+-------+------------------+---------------+----------------+--------+---------------+---------+------------+
| EmpId | Experience_Prior | Qualification | Specialization | Status | LastSubmitted | NextDue | ApprovedOn |
+-------+------------------+---------------+----------------+--------+---------------+---------+------------+

Right now, there is no data in MySkills_details. I have to make a Foreign key on EmpId in MySkills_details referencing EmpId in MySkills, which is not possible due to composite primary key in MySkills. So I decided to go the other way round. Except the insertion is happening in MySkills first and there are no BEFORE INSERT triggers in SQL Server as far as I know. So, How to write a trigger like BEFORE INSERT which inserts data in MySkill_details first before inserting in MySkills.

Solution

Please test following SQL Server Instead Of Trigger which checks first details table. In case there are missing data in Details, it inserts that table As the second step, it continue with inserting into Skills table

CREATE Trigger MySkillsInsteadOfInsert on dbo.MySkills Instead Of Insert
AS
Begin

    insert into MySkills_details (
        EmpId -- and other relevant columns
    )
    select i.EmpId -- and other relevant columns
    from inserted i
    left join MySkills_details d on i.EmpId = d.EmpId
    where d.EmpId is null

    Insert Into MySkills(EmpId) -- and other relevant columns
    Select EmpId -- and other relevant columns
    From inserted i;

End

For more samples on SQL Server instead Of trigger please refer to given example.

But please note my words, I think it will be an alternative design to keep skills in a different master table. And before inserting into details, in general we check that the master exists or not. So your control in general could be running in the opposite way. Users in general insert master data first. In this case the Skills table data. Then the details are populated.