SQL-Instead of 触发器

定义及优点

        INSTEAD OF触发器指定执行触发器而不是执行触发 的SQL 语句,从而替代触发语句的操作。
        在表或视图上,每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。并且,可以在每个具有 INSTEAD OF 触发器的视图上定义视图。

优点:

▶   使不能更新的视图支持更新。包含多个基表的视图必须使用 Instead Of 触发器来支持引用表中数据的插入、更新和删除操作

▶   拒绝批处理中的某些部分同时允许批处理的其它部分成功

▶   如果遇到错误情况则采取备用操作

 

After 触发器 与 Instead of 触发器

# 理解

       对于 “ after触发器只有执行某一操作insert、update、delete之后触发器才被触发,且只能定义在表上;instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。” 这句话我并不理解。

       但在无意间看到这一篇大佬的博客后(点击这串文字,到达该博主博客),我真正理解了instead of 触发器。

       after触发器是在操作成功后,所采取的一些动作,而对于instead of触发器,真正起作用的是触发器里面的动作!

        用文字不好将其中的道理解释清楚,所以请仔细理解以下得例子(例子也是来自那位大佬)。

# 举例

& 首先这里有两个表,一个是新闻类别表( caregory ),一个是新闻表( news )。顾名思义,每一个新闻都有一个类别属性,该属性对应在类别表中;每一个类别可以对应有新闻表中的多条新闻(不知道解释清楚没)。总的来说:news表的主键为id,外键为caID,与caregory的主键相对,此时通过主外键将两个表连接起来。(建立好后,先在其中插入数据)

 

♥♥♥  如下,创建了一个delete触发器,其功能为删除一个新闻类别,以及该新闻类别下所有的新闻

1 CREATE TRIGGER De_c
2 ON caregory
3 FOR DELETE
4 AS
5     DELETE news WHERE caid = ( SELECT id FROM deleted )

创建好后,实现语句:DELETE FROM caregory WHERE id = 2;

会出现以下报错

出错原因:after触发器是在执行完delete操作后执行的,执行操作后,该新闻类别已经不存在了,当触发器再执行删除新闻时,新闻类别已经找不到了,就无法删除掉新闻(因为 news 表有外键,需要在外键存在的情况下才能删除新闻)。这个时候 instead of 触发器就能很好的解决这一问题。

♥♥♥  如下,创建与delete 触发器同样功能的instead of 触发器

1 CREATE TRIGGER De_in
2 ON caregory
3 INSTEAD OF DELETE
4 AS
5     DELETE news WHERE caid = ( SELECT id FROM deleted );
6     DELETE caregory WHERE id = (  SELECT id FROM deleted  );

创建好后,实现语句:DELETE FROM caregory WHERE id = 2;

这一次就能删除掉该新闻类别及其类别下得所有新闻,无报错

   到了这里,我想就可以理解到那句话的意思了

▶ after 触发器(insert、update、delete触发器)内的语句是在操作执行之后(已经作用在表上)才触发执行的

▶ instead of 触发器 并不会执行操作,那个操作仿佛就是一个触发的命令,有了这个命令,instead of 触发器触发了,就会执行触发器内的语句;若触发器内只有像 raiserror 、print之类的不含操作性的语句,那该操作并不会真正的执行,但在触发器内可以通过 inserted 或 deleted 表中获取到本该执行该操作而形成的数据

 

Instead of 触发器 练习

 &练习一

以下有两个表(个人表 - people ,教师表 - teacher ,每一个人有一个家教老师):

# 1 : 创建一个视图,将学生与老师一一对应,直观的表现其对应关系

1 CREATE VIEW v_teacher AS 
2 SELECT people.pid,people.pname,people.psex,teacher.tid,teacher.salary FROM people inner join teacher on people.pid = teacher.pid;
3 go

查看视图如下:

# 2 :在视图上创建触发器,以达到在多个表中插入行。

要求:若插入行中的个人数据出现重复时,将此数据插入到peoplerepeat表中;若教师数据出现重复,则将教师数据进行更新。 

触发器如下(如有问题,欢迎指出):

IF EXISTS ( SELECT 1 FROM sys.objects WHERE name = 'tr_in_vt' AND type = 'TR' )
    DROP TRIGGER tr_in_vt;
GO
CREATE TRIGGER tr_in_vt
ON v_teacher
INSTEAD OF INSERT
AS
    DECLARE @pid int,@pname varchar(20),@psex varchar(2),@tid int,@salary money;
    SELECT TOP 1 @pid = pid ,@pname = pname,@psex = psex,@tid = tid,@salary = salary FROM inserted
    IF EXISTS ( SELECT 1 FROM people WHERE pid = ( SELECT TOP 1 pid  FROM inserted) )
    BEGIN
        INSERT INTO peoplerepeat values (@pid,@pname,@psex,USER_NAME(),getdate());
        raiserror('插入的数据重复,已将重复的数据放入peoplerepeat表!',16,10);    
    END
    ELSE
    BEGIN
        INSERT INTO people values (@pid,@pname,@psex);
        IF EXISTS ( SELECT 1 FROM teacher WHERE tid = ( SELECT TOP 1 tid  FROM inserted) )
            BEGIN    
            UPDATE teacher SET pid = @pid,salary = @salary WHERE tid = @tid;
            raiserror('插入的数据重复,已将重复的数据更新入teacher表!',16,10);
            END    
        ELSE
            INSERT INTO people values (@tid,@pid,@salary);
    END

 

§ 对于简单视图,可以直接执行INSERT,UPDATE和DELETE操作;但是对于复杂视图,不允许直接执行INSERT,UPDATE和DELETE操作。

   当视图中包含以下结构之一,就表示为不可更新的视图,都不允许直接执行DML操作

▶具有集合操作符(UNION,UNION ALL,INTERSECT,MINUS);

▶具有分组函数(MIN,MAX,SUM,AVG,COUNT等)统计函数;

▶具有GROUP BY,CONNECT BY或START WITH等子句,HAVING 子句;

▶具有DISTINCT关键字;

▶具有连接查询(集合运算连接);

▶CASE 或者DECODE 语句

 

&练习二

以下有两个表(员工表 emp ,部门表 dept):

# 1 :在视图上创建触发器,以达到在多个表中插入行。

要求:在插入员工数据时,对已经添加过的员工,不执行添加操作,其余不做限制。

触发器如下(如有问题,欢迎指出):

 1 CREATE TRIGGER v_i_tr
 2 on v_emp
 3 INSTEAD OF insert
 4 AS
 5 DECLARE @eid int,@did int,@ename varchar(20),@job varchar(20),@sal money,@dname varchar(20),@loc varchar(100);
 6 SELECT TOP 1 @eid = eid,@ename = ename,@job = job,@sal = sal,@dname = dname,@loc = loc,@did = did FROM inserted;
 7 IF not exists ( SELECT 1 FROM dept WHERE did = @did)
 8     INSERT INTO dept values (@did,@dname,@loc)
 9 IF exists ( SELECT 1 FROM emp WHERE eid = @eid)
10     RAISERROR('该员工已经添加过了,不用重复添加',16,10);
11 ELSE
12     INSERT INTO emp values (@eid,@ename,@job,@sal,@did);

# 2 :在视图上创建触发器,以达到在多个表中更新行。

触发器如下(如有问题,欢迎指出):

 1 CREATE TRIGGER v_u_tr
 2 ON v_emp
 3 INSTEAD OF UPDATE
 4 AS
 5 DECLARE @eid int,@did int,@ename varchar(20),@job varchar(20),@sal money,@dname varchar(20),@loc varchar(100);
 6 SELECT TOP 1 @eid = eid,@ename = ename,@job = job,@sal = sal,@dname = dname,@loc = loc,@did = did FROM inserted;
 7 IF @did = (SELECT TOP 1 did FROM deleted)
 8     UPDATE dept SET dname = @dname,loc = @loc WHERE did = @did;
 9 ELSE IF not EXISTS (SELECT 1 FROM dept WHERE did = @did)
10     INSERT INTO dept values(@did,@dname,@loc);
11 UPDATE emp SET ename = @ename,job = @job,sal = @sal,did = @did WHERE eid = @eid;

# 3 :在视图上创建触发器,以达到在多个表中删除行。

触发器如下(如有问题,欢迎指出):

 1 CREATE TRIGGER v_d_tr
 2 ON v_emp
 3 INSTEAD OF DELETE
 4 AS
 5 DECLARE @eid int,@did int;
 6 SELECT TOP 1 @eid = eid,@did = did FROM deleted;
 7 IF not EXISTS ( SELECT COUNT(1) FROM emp WHERE eid = @eid)
 8     RAISERROR('该员工不存在!无法删除',16,10);
 9 IF ( SELECT COUNT(1) FROM deleted ) > 1
10 BEGIN
11     print('删除了多条数据哟!');
12     DELETE emp WHERE did = @did;
13 END
14 ELSE
15     DELETE emp WHERE eid = @eid;

 

我只是一个快乐的搬运工,只是想自己汇总资料,方便查看

真正的博文请点击各标题就可以跳转过去;若侵犯到您的利益,请联系我,我立即删除!

posted @ 2021-09-01 15:54  Mra_m  阅读(212)  评论(0编辑  收藏  举报