SQL> desc mike_test0115;
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(40) Y create or replace trigger temp
before insert on mike_test0115
for each row
declarebegin
IF :new.ID = 0 then
DBMS_OUTPUT.PUT_LINE('Insert');
RAISE_APPLICATION_ERROR(-20055,'INVALID CODE');
END IF;
end temp;
---------------------------------------------------------------------
已连接到 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
已连接为 mike
SQL> select * from mike_test0115; ID NAME
---------- ----------------------------------------
1 Mike
2 JimmySQL> insert into mike_test0115 values(0,'test');insert into mike_test0115 values(0,'test')ORA-20055: INVALID CODE
ORA-06512: 在 "MIKE.TEMP", line 7
ORA-04088: 触发器 'MIKE.TEMP' 执行过程中出错SQL> select * from mike_test0115; ID NAME
---------- ----------------------------------------
1 Mike
2 Jimmy
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
ID NUMBER
NAME VARCHAR2(40) Y create or replace trigger temp
before insert on mike_test0115
for each row
declarebegin
IF :new.ID = 0 then
DBMS_OUTPUT.PUT_LINE('Insert');
RAISE_APPLICATION_ERROR(-20055,'INVALID CODE');
END IF;
end temp;
---------------------------------------------------------------------
已连接到 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
已连接为 mike
SQL> select * from mike_test0115; ID NAME
---------- ----------------------------------------
1 Mike
2 JimmySQL> insert into mike_test0115 values(0,'test');insert into mike_test0115 values(0,'test')ORA-20055: INVALID CODE
ORA-06512: 在 "MIKE.TEMP", line 7
ORA-04088: 触发器 'MIKE.TEMP' 执行过程中出错SQL> select * from mike_test0115; ID NAME
---------- ----------------------------------------
1 Mike
2 Jimmy
如果是要抛出异常,对于楼主这个例子,完全可以用check来实现,不需要触发器。
我想到的只有通过view+instead触发器来实现。
2 id number,
3 t date,
4 amount number
5 );Table created.SQL>
SQL> CREATE VIEW TABLE_A AS
2 SELECT * FROM TABLE_A_base;View created.SQL> CREATE OR REPLACE TRIGGER TABLE_A_insert
2 INSTEAD OF INSERT ON TABLE_A
3 BEGIN
4 if :NEW.amount>0 then
5 insert into TABLE_A_base(id, t, amount)
6 values (:NEW.id, :NEW.t, :NEW.amount);
7 end if;
8 END;
9 /Trigger created.SQL> insert into TABLE_A values(1,sysdate,1);1 row created.SQL> insert into TABLE_A values(2,sysdate,0);1 row created.SQL> commit;Commit complete.SQL> select * from table_a; ID T AMOUNT
---------- ------------------- ----------
1 2006-01-19 15:59:46 1SQL>