SQLServer存储过程/触发器 Demo

小灰灰 2021-01-20 05:45 663阅读 0赞

## 需求 ##

创建一个触发器。插入student表一条数据，根据student的表字段teacher\_name，去查询teacher表，获取teacher表字段的主键，然后重新更新到当前student表的teacher\_id字段。

本文还是先从存储过程编写开始说起吧。

### 1. 存储过程 ###

按照需求，我的思路就是编写一个存储过程，入参是student的主键id,以及teacher\_name，然后写sql进行update，具体如下：

USE  dbo  -- 对应的数据库
    GO
    	-- 创建存储过程 sp_student  入参 p_teacher_name 和 p_id
    	CREATE PROCEDURE 
    		sp_student @p_teacher_name CHAR(20) , @p_id CHAR(20) 
    	AS
    		UPDATE 
    			student s
    		SET
    			s.teacher_id = ( 
    					SELECT
    						t.id
    					FROM
    						teacher t
    					WHERE
    						t.teacher_name = @p_teacher_name 
    			)
    		WHERE
    			s.id = @p_id

尝试调用存储过程

EXEC.dbo.sp_student @p_id =1 ,@p_teacher_name = '张三'

如果调用存储过程，你的student表teacher\_id字段有关联上，那就没问题了。

### 2. 触发器编写 ###

上网查资料说SQLServer是不允许在触发器调用存储过程的，具体我也没去测试行不行，反正我就把上面的存储过程改一下就好了。先贴SQL:

CREATE TRIGGER tri_insert ON student AFTER INSERT AS
    IF
     EXISTS ( SELECT * FROM inserted ) 
     BEGIN
     
      -- 声明变量，这里注意 VARCHAR类型后面记得加上位数，不然默认好像是一位，这里我踩过坑。
     DECLARE
      @teacher_name  VARCHAR(20),@teacher_id VARCHAR(20); 
      
    -- SQLServer触发器里面，插入数据后，会把数据放到临时表inserted里面，所以我们只要在触发器的sql里面,从这个表里面select你想要的字段，就可以拿到当前数据插入后对应字段的值了。
    
     SELECT
      @teacher_name  = teacher_name 
     FROM
      inserted;
      
      -- 根据刚从临时表获取的@teacher_name的值（即当前触发器刚插入数据字段teacher_name字段的值）
      -- 查询teacher表获取id,并且赋给变量@teacher_id
     SELECT
      @teacher_id = id  
     FROM
    	  teacher
     WHERE
    	  teacher_name = @teacher_name;
    	  
    -- 根据id 直接更新teacher_id的值
     UPDATE 
     	student 
     SET 
     	teacher_id = @teacher_id 
     WHERE
      ID = ( SELECT id FROM inserted );  -- 直接定位到当前更新的数据
    
    END

最后一点调试小技巧：  
把变量的值set到无关紧要字段，来看看变量的值，是什么，或者set个固定的值到无关紧要的字段，确定有没有写错，不过一般语法错误都会构建失败的啦