SQL Server - BEFORE INSERT 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40014056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - BEFORE INSERT Trigger
提问by ShyProgrammer
I have two tables as follows:
我有两个表如下：
Table 1 'MySkills' [ primary key (EmpId, SkillId)]
表 1 'MySkills' [主键 (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)]
和表 1 'MySkills_details' [主键 (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.
目前，MySkills_details 中没有数据。我必须在 MySkills_details 中的 EmpId 上创建一个外键，引用 MySkills 中的 EmpId，由于 MySkills 中的复合主键，这是不可能的。所以我决定反其道而行之。除了插入首先发生在 MySkills 中，据我所知，SQL Server 中没有 BEFORE INSERT 触发器。那么，如何编写像 BEFORE INSERT 这样的触发器，它先在 MySkill_details 中插入数据，然后再插入 MySkills。
采纳答案by Eralper
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
请按照检查第一个详细信息表的 SQL Server 而不是触发器进行测试。如果Details中缺少数据，它会插入该表作为第二步，它继续插入到Skills表中
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 triggerplease refer to given example.
有关SQL Server 而不是触发器的更多示例，请参阅给定的示例。
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.
但是请注意我的话，我认为将技能保留在不同的主表中将是另一种设计。在插入细节之前，通常我们会检查 master 是否存在。因此，您的控制通常可能以相反的方式运行。用户一般先插入主数据。在本例中为技能表数据。然后填充详细信息。
