SQL Server-插入前触发器 [英] SQL Server - BEFORE INSERT Trigger

问题描述

我有两个表，如下所示：

I have two tables as follows:

表1'MySkills'[主键（EmpId，SkillId）]

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           |
+----------+------------+---------+----------+-----------+------------+-----------------+----------------+

表1'MySkills_details'[主键（EmpId ）]

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

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

现在，没有数据MySkills_details。
我必须在MySkills_details中的EmpId上引用外键，以引用MySkills中的EmpId，由于MySkills中的复合主键，这是不可能的。
所以我决定反过来。据我所知，除了插入是首先在MySkills中进行的，而且在SQL Server中没有INSERT触发器之前。
因此，如何编写类似BEFORE INSERT的触发器，该触发器先将数据插入MySkill_details，然后再插入MySkills。

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.

推荐答案

请测试以下SQL Server而不是触发器，它会检查第一个详细信息表。
如果详细信息中缺少数据，则将其插入表
。第二步，继续将其插入到技能表中

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

有关使用SQL Server代替触发器，请参阅给定的示例。

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

但是请注意我的话，我认为将技能保留在其他主表中将是一种替代设计。
在插入细节之前，通常我们检查母版是否存在。
因此，您的控件通常可能以相反的方式运行。
通常，用户首先插入主数据。在这种情况下，Skills表数据。
然后填充详细信息。

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.

这篇关于SQL Server-插入前触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！