Instead of trigger to update view with multiple ta

2019-06-27 06:50发布

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

That is I want update more than one table that this view selects from.

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

回答1:

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');

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.

Here is SQLFiddle demo