Instead-of触发器

DML触发器除了执行insert，update或delete操作以外，还要执行触发器主体的命令。
Instead-of触发器会用触发器主体的命令替换相应的DML操作(触发的insert,update,delete都没有执行，仅执行了触发器主体的命令)
Instead-of 触发器常用于管理编写不可更新的视图

1.Instead-of触发器用触发器主体的命令替换相应的DML操作。
现在需要实现：向表student 插入数据时，检查id是否存在于表student中，如存在则进行更新操作，否则就不更新且输出提示信息。

create trigger CheckId
insetead of update     --替换原来的update
as
if not exists(select * from student where id=:new.id)
    begin
        rollback transaction
        DBMS_OUTPUT.PUT_LINE('要处理记录的学生ID不存在');
    end;
else
    begin
    update student
    set first_name=:new.first_name;
    where id= :new.id;
    end;
end CheckId;
/

--触发 trigger CheckId
update student
set first_name='David'
where id='12721205'     --其实没有实现这个update，而是实现了CheckId的主体

 

2. instead-of触发器常用于管理编写不可更新的视图
->1.不可更新视图
    使用了集合操作运算符(UNION,UNION ALL ,INTERSECT,MINUS)
    使用了分组函数(MIN,MAX,SUM,AVG)
    使用了GROUP BY ,CONNECT BY ,START WITH 子句
    使用了DISTINCT 关键字
    使用了连接查询
对于基于上述情况创建的视图，不能对其直接执行DML，但可以在该视图上创建INSTEAD OF触发器来间接执行DML。

->2. 使用Instead-of触发器解决不可更新视图示例
books表字段：issbn,title,athour_id1,author_id2,author_id3

books表

ISBN	Title	Author1	Author2	Author3
72223855	Oracle 9i	38

authors表字段:id，first_name,last_name

authors表

ID	First_name	Last_name
38	Robet	Freeman

　books_authors 视图

books_authors 视图

ISBN	Title	First_name	Last_name
72223855	Oracle 9i	Robet	Freeman

 

　　用表 books 和author表，创建视图books_authors

create or replace view books_authors as
    select b.isbn,b.title,a.first_name,a.last_name
    from books b
    join authors a
    on b.authors_id1=a.id
    or b.authors_id2=a.id
    or b.authors_id3=a.id;

　　执行DML会报错(在不可更新视图上不能使用DML)

insert into books_authors(isbn,title,first_name,last_name)
vaules('72223855','SQL Cookbook','Joe','Blow');

 

解决方案：
用instead-of触发器来实现insert语句的正常操作，即更新两个底层表。

create or replace trigger InsertBooksAuthors
    instead of insert on books_authors   --这个insert 替换了前面的insert，它没有直接更新不可更新视图，而是更新生成它的两个底层表
declare
    v_book books%rowtype;
    v_author_id authors.id%type;
begin
    begin
        select id
        into v_author_id
        from authors
        where first_name= :new.first_name
        and last_name= :new.last_name;    --如果插入的记录(first_name,last_name 在books表中存在，就取出这条记录)
    exception
        when no_data_found then
        insert into authors(id，first_name,last_name)
        vaules(author_sequence.nextval,:new.first_name,:new.last_name)
        returning id into v_author_id;   --如果不存在则重新插入到authors表
    end;

    select *
    into v_book
    from books
    where isbn= :new.isbn;

    if v_book.author_id2 is null then
        update books
        set author_id2=v_author_id
        where isbn= :new.isbn;
    else
        update books
        set author_id3=v_author_id
        where isbn= :new.isbn;
    end if;
end InsertBooksAuthors;

 

Books

ISBN	Title	Author1	Author2	Author3
72223855	Oracle 9i	38	1000

 

authors

ID	First_name	Second_name
38	Robet	Freeman
1000	Esetrt	Elegmen

 

books_authors

ISBN	Title	First_name	Last_name
72223855	Oracle 9i	Robet	Freeman
72223855	Oracle 9i	Esetrt	Elegmen

 

 

转载于:https://www.cnblogs.com/qianwen/p/3766504.html