在具有额外常量列的表上插入触发器到新表中？(Insert trigger on a table with extra constant column in to new table?)

在具有额外常量列的表上插入触发器到新表中？(Insert trigger on a table with extra constant column in to new table?)

我一直在研究一个数据库，它包含两个模式名称作为前端和备份。 在一个表名称中的位置：

front.Details studID SemID GPA 100 1 4 200 2 3

另一个表名是：

backup.DetailsV studID DEPT SemID GPA

表backup.DetailsV中的输出应如下所示：

studID DEPT SemID GPA 100 1 1 4 200 1 2 3 100 2 1 4 200 2 2 3

如何在表格详细信息上创建触发器以使用dept id 1和2插入到表DetailsV中两次？

I've been working on a database which consists of two schemas names as front and backup. Where in one table name:

front.Details studID SemID GPA 100 1 4 200 2 3

Another table name is:

backup.DetailsV studID DEPT SemID GPA

The output in Table backup.DetailsV should look like below:

studID DEPT SemID GPA 100 1 1 4 200 1 2 3 100 2 1 4 200 2 2 3

How can I create trigger on table Details to insert in to table DetailsV twice with dept id 1 and 2?

最满意答案

要继续Damien的想法，如果使用DetailsV表的唯一原因是生成该输出，则可以使用视图轻松完成。 如果它只是读取数据，则存储过程不知道或不关心源是表还是视图。

Select studID, 1 as Dept, SemID, GPA From front.Details UNION ALL Select studID, 2 as Dept, SemID, GPA From front.Details

如果您需要保留数据流经front.Details表，或者如果您需要在报告之前操作该数据，则只需保留备份表。 如果这确实是你想要的，那么触发器查询非常相似，但是不是寻址表，而是使用特殊的[inserted]表来获取新值。

Select studID, 1 as Dept, SemID, GPA From inserted UNION ALL Select studID, 2 as Dept, SemID, GPA From inserted

To continue Damien's thought, if the only reason to have the DetailsV table is to generate that output, you can easily do that with a view. If it is just reading data, the stored procedure doesn't know or care if the source is a table or a view.

Select studID, 1 as Dept, SemID, GPA From front.Details UNION ALL Select studID, 2 as Dept, SemID, GPA From front.Details

You would only keep a backup table if you needed to keep a history of the data flowing through the front.Details table, or if you needed to manipulate that data before reporting it out. If that is really what you want, the trigger query is very similar, but instead of addressing the table, you use the special [inserted] table to get the new values.

Select studID, 1 as Dept, SemID, GPA From inserted UNION ALL Select studID, 2 as Dept, SemID, GPA From inserted