代替触发更新具有多个表的视图 [英] Instead of trigger to update view with multiple tables

问题描述

我正在尝试找到一个示例，该示例如何使用触发器而不是触发器来更新多个表上的视图。

I am trying to find an example of how to update a view on multiple tables using an instead of trigger.

我想更新多个表

我找不到任何示例。如果有人可以向我展示如何做到这一点，那就太好了。

I cant find any examples. If someone can show me how to this that would be great.

推荐答案

假设您正在使用SQLServer，这里是一个过于简化示例

Assuming that you're using SQLServer here is one oversimplified example

CREATE TABLE persons
(personid  int, 
 firstname varchar(32), 
 lastname  varchar(32));

CREATE TABLE employees
(employeeid int, 
 personid   int, 
 title      varchar(32));

CREATE VIEW vwEmployees AS
SELECT p.personid, employeeid, firstname, lastname, title
  FROM employees e JOIN persons p
    ON e.personid = p.personid;

CREATE TRIGGER tgEmployeesInsert ON vwEmployees
INSTEAD OF INSERT AS
BEGIN
  INSERT INTO persons (personid, firstname, lastname)
  SELECT personid, firstname, lastname
    FROM INSERTED

  INSERT INTO employees (employeeid, personid, title)
  SELECT employeeid, personid, title
    FROM INSERTED
END;

INSERT INTO vwEmployees (personid, employeeid, firstname, lastname, title)
VALUES(1, 1, 'Jhon', 'Doe', 'SQL Developer');

注意：实际上，您肯定必须处理 IDENTITY 列以及SQL Server中的触发器是语句而不是行范围的事实。

Note: In reality you will most certainly have to deal with IDENTITY columns and the fact that triggers in SQL Server are statement rather the row scoped.

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于代替触发更新具有多个表的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！