Oracle触发器拼接字段

1. 环境准备

1.1 Sql语句

CREATE TABLE STUDENT(
  ID int,
  NAME varchar2(20),
  COURSE varchar2(20),
  SCORE int,
  LEV varchar2(3),
  SEARCH varchar2(100)
);

2. Oracle触发器

2.1 基本结构

CREATE [OR REPLACE] TRIGGER trigger_name  
 {BEFORE | AFTER }  
 {INSERT | DELETE | UPDATE [OF column [, column …]]}  
 [OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]  
 ON [schema.]table_name | [schema.]view_name   
 [REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]  
 [FOR EACH ROW ]  
 [WHEN condition]  

2.2 结构简化

create or replace trigger 触发器名  
after update of 字段  
on 表  
for each row  
when (new.字段=条件) 

3. 创建触发器

CREATE OR REPLACE TRIGGER UPDATE_STUDENT
before update or insert on STUDENT
for each row                                                                                                     
begin
    if updating('SEARCH') then --如果更新的字段包括Search不更新
      return;
    end if;
    select :new.ID || ',' ||  :new.NAME || ',' ||  :new.COURSE || ',' ||  :new.SCORE || ',' ||  :new.LEV into :new.SEARCH from dual;
end;  

4. 试验触发器是否成功

insert into student(ID,NAME,COURSE,SCORE,LEV) VALUES(1,'张三','数学',98,'优');
commit;