oracle---触发器

　　CREATE [OR replaCE] TIGGER触发器名 触发时间 触发事件 　　ON表名/视图名 　　[FOR EACH ROW] //加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器 　　BEGIN 　　pl/sql语句 　　END

    create [or replace] trigger [scheR_909_11845@a.]trigger_name {Before | after | instead of} {delete [or insert][or update [of column,...n]]} on [scheR_909_11845@a.]table_name | vIEw_name [for each row [when(condition)]] sql_statement[,...n] 

　　CREATE OR replaCE trigGER trigger_name 　　< before | after | instead of > < insert | update | delete> ON table_name 　　[FOR EACH ROW] 　　WHEN (condition) 　　DECLARE 　　BEGIN 　　END;

CREATE [OR replaCE] trigGER trigger_name INSTEAD OF {INSERT|deletE|updatE [OF columN...]} [OR {INSERT| deletE| updatE [OF columN...]}] ON VIEW_name [REFFERENCING{olD [AS] olD | NEW [AS] NEW| PARENT AS PARENT}] // 可以指定相关名称，当前的默认相关名称为olD和NEW,
　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　应用相关名称时需要加： [FOR EACH ROW] //instead of 触发器只能在行级上触发，因为没有必要指定 [WHEN CONDITION] DECLARE
BEGIN END;

CREATE table "HNZC"."trigGERTEST" ( "ID" VARCHAR2(20 BYTE),"name" VARCHAR2(20 BYTE),"score" numbER ); create table tab1 SELEct * from triggertest;

CREATE OR replaCE trigGER trigGER1 AFTER INSERT ON trigGERTEST //插入后触发 FOR EACH ROW //行级触发器 BEGIN INSERT INTO tab1(ID,Name) VALUES(‘22‘,‘33‘); END;

insert into triggertest (ID) values (‘aabbcc‘);

CREATE OR replaCE trigGER trigGER1 AFTER INSERT ON trigGERTEST FOR EACH ROW BEGIN IF(TO_CHAR(sysdate,‘DAY‘) IN (‘星期三‘,‘星期天‘)) THEN RAISE_APPliCATION_ERROR(-20001,‘不是上班时间，不能修改表格triggertest‘); END IF; END;

insert into triggertest (ID) values (‘aabbcc‘);

在行 1 上开始执行命令时出错: insert into triggertest (ID) values (‘aabbcc‘) 错误报告: sql 错误: ORA-20001: 不是上班时间，不能修改表格triggertest ORA-06512: 在 "HNZC.trigGER1",line 3 ORA-04088: 触发器 ‘HNZC.trigGER1‘ 执行过程中出错

CREATE OR replaCE trigGER trigGER1 BEFORE INSERT OR deletE OR updatE ON trigGERTEST FOR EACH ROW BEGIN IF(TO_CHAR(sysdate,‘DAY‘) IN (‘星期六‘,‘星期天‘)) OR(TO_CHAR(sysdate,‘HH24:MI‘) NOT betweeN ‘9:00‘ AND ‘18:00‘) THEN RAISE_APPliCATION_ERROR(-20001,‘不是上班时间，不能修改表格triggertest‘); END IF; END;

CREATE OR replaCE trigGER trigGER1 BEFORE INSERT OR deletE OR updatE ON trigGERTEST FOR EACH ROW WHEN(olD.name=‘月儿‘) BEGIN CASE WHEN updatING(‘score‘) THEN IF:NEW.score<:olD.score THEN RAISE_APPliCATION_ERROR(-20001,‘月儿的分数只能提升不能下降‘); END IF; END CASE; END;

updatE "HNZC"."trigGERTEST" SET score = ‘10‘ WHERE ROWID = ‘AAAdEzAAPAAAAH+AAB‘ AND ORA_ROWSCN = ‘47685303‘ ORA-20001: 月儿的分数只能提升不能下降 ORA-06512: 在 "HNZC.trigGER1",line 4 ORA-04088: 触发器 ‘HNZC.trigGER1‘ 执行过程中出错

updatE "HNZC"."trigGERTEST" SET score = ‘30‘ WHERE ROWID = ‘AAAdEzAAPAAAAH+AAB‘ AND ORA_ROWSCN = ‘47685303‘ 提交成功

CREATE OR replaCE trigGER trigGER1 BEFORE INSERT OR deletE OR updatE ON trigGERTEST FOR EACH ROW BEGIN TESTPRO1(); END;

create or replace PROCEDURE TESTPRO1 AS BEGIN insert into tab1(ID,name,score) VALUES(‘AAA‘,‘BBB‘,200); END TESTPRO1;

create or replace PROCEDURE TESTPRO1 AS BEGIN insert into tab1(ID,200); END TESTPRO1;

update  triggertest set name= ‘水儿‘ where name=‘月儿‘;

CREATE OR replaCE VIEW stuDNET_VIEW AS SELECT CLASSID,AVG(score) AVERAGE_score FROM stuDENT GROUP BY CLASSID;

deletE FROM stuDNET_VIEW WHERE CLASSID=‘111‘;

错误报告:
sql 错误: ORA-01732: 此视图的数据操纵操作非法 01732. 00000 - "data manipulation operation not legal on this vIEw"

CREATE OR replaCE trigGER stuDENT_VIEW_deletE INSTEAD OF deletE ON stuDNET_VIEW FOR EACH ROW BEGIN deletE FROM stuDENT WHERE CLASSID=:olD.CLASSID; END stuDENT_VIEW_deletE;

deletE FROM stuDNET_VIEW WHERE CLASSID=‘111‘;

1 行已删除。