SQLInsteadof触发器

  • Z时代
  • 2024-01-10
  • 分类：综合

database

定义及优点

        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触发器，其功能为删除一个新闻类别，以及该新闻类别下所有的新闻

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

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

会出现以下报错：

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

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

1CREATETRIGGER De_in
2ON caregory
3 INSTEAD OFDELETE
4AS
5DELETE news WHERE caid = ( SELECT id FROM deleted );
6DELETE 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 ： 创建一个视图，将学生与老师一一对应，直观的表现其对应关系

1CREATEVIEW v_teacher AS
2SELECT people.pid,people.pname,people.psex,teacher.tid,teacher.salary FROM people innerjoin teacher on people.pid = teacher.pid;
3go

查看视图如下：

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

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

触发器如下（如有问题，欢迎指出）：

IFEXISTS ( SELECT1FROM sys.objects WHERE name ="tr_in_vt"AND type ="TR" )
DROPTRIGGER tr_in_vt;
GO
CREATETRIGGER tr_in_vt
ON v_teacher
INSTEAD OFINSERT
AS
DECLARE@pidint,@pnamevarchar(20),@psexvarchar(2),@tidint,@salarymoney;
SELECTTOP1@pid= pid ,@pname= pname,@psex= psex,@tid= tid,@salary= salary FROM inserted
IFEXISTS ( SELECT1FROM people WHERE pid = ( SELECTTOP1 pid  FROM inserted) )
BEGIN
INSERTINTO peoplerepeat values (@pid,@pname,@psex,USER_NAME(),getdate());
raiserror("插入的数据重复，已将重复的数据放入peoplerepeat表！",16,10);    
END
ELSE
BEGIN
INSERTINTO people values (@pid,@pname,@psex);
IFEXISTS ( SELECT1FROM teacher WHERE tid = ( SELECTTOP1 tid  FROM inserted) )
BEGIN
UPDATE teacher SET pid =@pid,salary =@salaryWHERE tid =@tid;
raiserror("插入的数据重复，已将重复的数据更新入teacher表！",16,10);
END
ELSE
INSERTINTO 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 ：在视图上创建触发器，以达到在多个表中插入行。

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

触发器如下（如有问题，欢迎指出）：

 1CREATETRIGGER v_i_tr
 2on v_emp
 3 INSTEAD OFinsert
 4AS
 5DECLARE@eidint,@didint,@enamevarchar(20),@jobvarchar(20),@salmoney,@dnamevarchar(20),@locvarchar(100);
 6SELECTTOP1@eid= eid,@ename= ename,@job= job,@sal= sal,@dname= dname,@loc= loc,@did= did FROM inserted;
 7IFnotexists ( SELECT1FROM dept WHERE did =@did)
 8INSERTINTO dept values (@did,@dname,@loc)
 9IFexists ( SELECT1FROM emp WHERE eid =@eid)
10RAISERROR("该员工已经添加过了，不用重复添加",16,10);
11ELSE
12INSERTINTO emp values (@eid,@ename,@job,@sal,@did);

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

触发器如下（如有问题，欢迎指出）：

 1CREATETRIGGER v_u_tr
 2ON v_emp
 3 INSTEAD OFUPDATE
 4AS
 5DECLARE@eidint,@didint,@enamevarchar(20),@jobvarchar(20),@salmoney,@dnamevarchar(20),@locvarchar(100);
 6SELECTTOP1@eid= eid,@ename= ename,@job= job,@sal= sal,@dname= dname,@loc= loc,@did= did FROM inserted;
 7IF@did= (SELECTTOP1 did FROM deleted)
 8UPDATE dept SET dname =@dname,loc =@locWHERE did =@did;
 9ELSEIFnotEXISTS (SELECT1FROM dept WHERE did =@did)
10INSERTINTO dept values(@did,@dname,@loc);
11UPDATE emp SET ename =@ename,job =@job,sal =@sal,did =@didWHERE eid =@eid;

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

触发器如下（如有问题，欢迎指出）：

 1CREATETRIGGER v_d_tr
 2ON v_emp
 3 INSTEAD OFDELETE
 4AS
 5DECLARE@eidint,@didint;
 6SELECTTOP1@eid= eid,@did= did FROM deleted;
 7IFnotEXISTS ( SELECTCOUNT(1) FROM emp WHERE eid =@eid)
 8RAISERROR("该员工不存在！无法删除",16,10);
 9IF ( SELECTCOUNT(1) FROM deleted ) >1
10BEGIN
11print("删除了多条数据哟！");
12DELETE emp WHERE did =@did;
13END
14ELSE
15DELETE emp WHERE eid =@eid;

 

以上是 SQLInsteadof触发器 的全部内容， 来源链接： utcz.com/z/535909.html

回到顶部