oracle触发器写法(oracle触发器条件判断)

　　oracle触发器写法

oracle触发器的写法是什么
一、触发器简介
触发器的界说便是说某个条件成立的时分，触发器里边所界说的句子就会被主动的履行。
因而触发器不需要人为的去调用，也不能调用。
然后，触发器的触发条件其实在你界说的时分就现已设定好了。
这里边需要说明一下，触发器能够分为句子级触发器和行级触发器。
详细的介绍能够参阅网上的资料，简单的说便是句子级的触发器能够在某些句子履行前或履行后被触发。而行级触发器则是在界说的了触发的表中的行数据改动时就会被触发一次。
具体举例：
1、在一个表中界说的句子级的触发器，当这个表被删去时，程序就会主动履行触发器里边界说的操作过程。这个便是删去表的操作便是触发器履行的条件了。
2、在一个表中界说了行级的触发器，那当这个表中一行数据发生变化的时分，比如删去了一行记载，那触发器也会被主动履行了。
二、触发器语法
触发器的语法：
create[orreplace]tigger触发器名触发时刻触发事件
on表名
[foreachrow]
begin
pl/sql句子
end
其间：
触发器名：触发器目标的称号。由于触发器是数据库主动履行的，因而该称号仅仅一个称号，没有实质的用途。
触发时刻：指明触发器何时履行，该值可取：
before：表明在数据库动作之前触发器履行;
after：表明在数据库动作之后触发器履行。
触发事件：指明哪些数据库动作会触发此触发器：
insert：数据库刺进会触发此触发器;
update：数据库修改会触发此触发器;
delete：数据库删去会触发此触发器。
表名：数据库触发器所在的表。
foreachrow：对表的每一行触发器履行一次。假如没有这一选项，则只对整个表履行一次。
触发器能完成如下功用：
功用：
1、允许/约束对表的修改
2、主动生成派生列，比如自增字段
3、强制数据一致性
4、供给审计和日志记载
5、防止无效的事务处理
6、启用杂乱的业务逻辑
举例
1)、下面的触发器在更新表tb_emp之前触发，意图是不允许在周末修改表：
createorreplacetriggerauth_securebeforeinsertorupdateorDELETE
ontb_emp
begin
IF(to_char(sysdate,’DY’)=’星期日’)THEN
RAISE_APPLICATION_ERROR(-20600,’不能在周末修改表tb_emp’);
ENDIF;
END;
/
2)、使用触发器完成序号自增
创立一个测试表：
createtabletab_user(
idnumber(11)primarykey,
usernamevarchar(50),
passwordvarchar(50)
);
创立一个序列：
代码如下:
createsequencemy_seqincrementby1startwith1nomaxvaluenocyclecache20;
创立一个触发器：
CREATEORREPLACETRIGGERMY_TGR
BEFOREINSERTONTAB_USER
FOREACHROW–对表的每一行触发器履行一次
DECLARE
NEXT_IDNUMBER;
BEGIN
SELECTMY_SEQ.NEXTVALINTONEXT_IDFROMDUAL;
:NEW.ID:=NEXT_ID;–:NEW表明新刺进的那条记载
END;
向表刺进数据:
insertintotab_user(username,password)values(‘admin’,’admin’);
insertintotab_user(username,password)values(‘fgz’,’fgz’);
insertintotab_user(username,password)values(‘test’,’test’);
COMMIT;
查询表结果：SELECT*FROMTAB_USER;

oracle触发器条件判断

触发器是特定事情出现的时分，主动履行的代码块。相似于存储进程，可是用户不能直接调用他们。触发器是许多联系数据库系统都供给的一项技能。在ORACLE系统里，触发器相似进程和函数，都有声明，履行和异常处理进程的PL/SQL块。
1.阐明
1)触发器是一种特殊的存储进程，触发器一般由事情触发而且不能承受参数，存储器由句子块去调用
2)触发器分类：
1.DML触发器：创立在表上，由DML事情引发
2.insteadof触发器：创立在视图上而且只能内行级上触发，用于替代insert，delete等操作(由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法)
3.DDL触发器：触发事情时数据库目标的创立和修正
4.数据库事情触发器：界说在数据库或许形式上，由数据库事情触发
3)组成：
1.触发事情：引发触发器被触发的事情DML句子(INSERT,UPDATE,DELETE句子对表或视图履行数据处理操作)、DDL句子(如CREATE、ALTER、DROP句子在数据库中创立、修正、删去形式目标)、数据库系统事情
(如系统启动或退出、异常过错)、用户事情(如登录或退出数据库)。
2.触发时刻：即该触发器是在触发事情产生之前(BEFORE)仍是之后(AFTER)触发
3.触发操作：触发器触发后要完结的事情
4.触发目标：包含表、视图、形式、数据库。只有在这些目标上产生了符合触发条件的触发事情，触发器才会履行触发操作。
5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事情才会主动履行触发操作。
6.触发频率：阐明触发器内界说的动作被履行的次数。即句子级(STATEMENT)触发器和行级(ROW)触发器。(比方delete多条数据时，行级触发器可能会履行多次，句子级触发器只会触发一次)
2.语法
1)阐明
不同类型的触发器例如DML触发器，Insteadof触发器，系统触发器语法格式区别较大
2)一般语法
CREATE[ORREPLACE]TIGGER触发器名触发时刻触发事情ON表名/视图名[FOREACHROW]//加上FOREACHROW即为行级触发器，不加时为句子级触发器BEGINpl/sql句子END
create[orreplace]trigger[schema.]trigger_name{before|after|insteadof}{delete[orinsert][orupdate[ofcolumn,…n]]}on[schema.]table_name|view_name[foreachrow[when(condition)]]sql_statement[,…n]
例如：
CREATEORREPLACETRIGGERtrigger_name<before|after|insteadof><insert|update|delete>ONtable_name[FOREACHROW]WHEN(condition)DECLAREBEGINEND;
3)insteadof触发器语法
语法：
CREATE[ORREPLACE]TRIGGERtrigger_nameINSTEADOF{INSERT|DELETE|UPDATE[OFCOLUMN…]}[OR{INSERT|DELETE|UPDATE[OFCOLUMN…]}]ONVIEW_NAME[REFFERENCING{OLD[AS]OLD|NEW[AS]NEW|PARENTASPARENT}]//可以指定相关称号，当时的默许相关称号为OLD和NEW,应用相关称号时需求加：[FOREACHROW]//insteadof触发器只能内行级上触发，由于没有必要指定[WHENCONDITION]DECLAREBEGINEND;
阐明：INSTEADOF用于对视图的DML触发，由于视图可能有多个表进行联合而成，因而并非一切的联合均可更新，运用INSTEADOF触发器可完结相应的操作。
3.实例
创立测试表格：
CREATETABLE”HNZC”.”TRIGGERTEST”(“ID”VARCHAR2(20BYTE),”NAME”VARCHAR2(20BYTE),”SCORE”NUMBER);createtabletab1select*fromtriggertest;
1)DML触发器/行级触发器
触发器如下：
CREATEORREPLACETRIGGERTRIGGER1AFTERINSERTONTRIGGERTEST//插入后触发FOREACHROW//行级触发器BEGININSERTINTOtab1(ID,NAME)VALUES(’22’,’33’);END;
履行句子：
insertintotriggertest(id)values(‘aabbcc’);
句子履行结束，表tab1中新添加一条数据
2)约束对表的修正(例如非工作时刻不能修正某些表)
触发器如下：
CREATEORREPLACETRIGGERTRIGGER1AFTERINSERTONTRIGGERTESTFOREACHROWBEGINIF(TO_CHAR(SYSDATE,’DAY’)IN(‘星期三’,’星期天’))THENRAISE_APPLICATION_ERROR(-20001,’不是上班时刻，不能修正表格triggertest’);ENDIF;END;
履行句子：
insertintotriggertest(id)values(‘aabbcc’);
今天周三因而输出成果为：
内行1上开端履行命令时犯错:insertintotriggertest(id)values(‘aabbcc’)过错报告:SQL过错:ORA-20001:不是上班时刻，不能修正表格triggertestORA-06512:在”HNZC.TRIGGER1″,line3ORA-04088:触发器’HNZC.TRIGGER1’履行进程中犯错
一般对表的修正约束如下(即周一至周五9——18点能修正表格)
CREATEORREPLACETRIGGERTRIGGER1BEFOREINSERTORDELETEORUPDATEONTRIGGERTESTFOREACHROWBEGINIF(TO_CHAR(SYSDATE,’DAY’)IN(‘星期六’,’星期天’))OR(TO_CHAR(SYSDATE,’HH24:MI’)NOTBETWEEN’9:00’AND’18:00′)THENRAISE_APPLICATION_ERROR(-20001,’不是上班时刻，不能修正表格triggertest’);ENDIF;END;
3)添加约束条件(如不能更改某个员工的记录)
触发器如下：(如下完成月儿的分数只能添加)
CREATEORREPLACETRIGGERTRIGGER1BEFOREINSERTORDELETEORUPDATEONTRIGGERTESTFOREACHROWWHEN(OLD.NAME=’月儿’)BEGINCASEWHENUPDATING(‘SCORE’)THENIF:NEW.SCORE<:old.scorethenraise_application_errorendifcase>
当时月儿的分数为20
当修正为10时犯错
UPDATE”HNZC”.”TRIGGERTEST”SETSCORE=’10’WHEREROWID=’AAAdEzAAPAAAAH+AAB’ANDORA_ROWSCN=’47685303’ORA-20001:月儿的分数只能提高不能下降ORA-06512:在”HNZC.TRIGGER1″,line4ORA-04088:触发器’HNZC.TRIGGER1’履行进程中犯错
当修正为30时成功
UPDATE”HNZC”.”TRIGGERTEST”SETSCORE=’30’WHEREROWID=’AAAdEzAAPAAAAH+AAB’ANDORA_ROWSCN=’47685303’提交成功
4)在触发器中调用存储进程
触发器为：
CREATEORREPLACETRIGGERTRIGGER1BEFOREINSERTORDELETEORUPDATEONTRIGGERTESTFOREACHROWBEGINTESTPRO1();END;
存储进程为：
createorreplacePROCEDURETESTPRO1ASBEGINinsertintotab1(id,name,score)VALUES(‘AAA’,’BBB’,200);ENDTESTPRO1;
履行完毕后tab1中添加一条数据
5)级联更新
触发器如下(triggertest表中name修正时一同修正tab1中的name)
createorreplacePROCEDURETESTPRO1ASBEGINinsertintotab1(id,name,score)VALUES(‘AAA’,’BBB’,200);ENDTESTPRO1;
履行句子：
updatetriggertestsetname=’水儿’wherename=’月儿’;
成果：tab1中name为月儿的也更改为水儿
6)insteadof触发器
TABLESTUDENT表格数据如下
cec6396ff8577a474d047f639959b56c.png
创立视图student_view
CREATEORREPLACEVIEWSTUDNET_VIEWASSELECTCLASSID,AVG(SCORE)AVERAGE_SCOREFROMSTUDENTGROUPBYCLASSID;
视图数据如下：
9a451841f8bf354bcb7bed9774abe1e4.png
对视图student_view履行如下操作：
DELETEFROMSTUDNET_VIEWWHERECLASSID=’111′;
履行成果：
过错报告:SQL过错:ORA-01732:此视图的数据操作操作不合法01732.00000-“datamanipulationoperationnotlegalonthisview”
解决方法：创立INSTEADOF视图
CREATEORREPLACETRIGGERSTUDENT_VIEW_DELETEINSTEADOFDELETEONSTUDNET_VIEWFOREACHROWBEGINDELETEFROMSTUDENTWHERECLASSID=:OLD.CLASSID;ENDSTUDENT_VIEW_DELETE;
履行删去句子
DELETEFROMSTUDNET_VIEWWHERECLASSID=’111′;
履行成果：删去成功
1行已删去。
4.注意事项
1)在触发器的履行部分只能用DML句子(SELECT、INSERT、UPDATE、DELETE)，不能使用DDL句子(CREATE、ALTER、DROP)
2)触发器中不能使用commit句子，触发器的操作与触发事情(INSERT,UPDATE,DELETE)一同进行COMMIT和ROLLBACK;
3)一个表上的触发器越多，关于表的DML操作性能影响越大
4)触发器最大为32K