如果你仅仅是想用trigger做一个插入或者修改前的校验,给两个例子你参考一下 1, CREATE OR REPLACE TRIGGER banchetto_trg BEFORE INSERT OR UPDATE OF cod_fiscale ON banchetto REFERENCING NEW AS n FOR EACH ROW WHEN (n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM azienda a) AND n.cod_fiscale NOT IN (SELECT p.cod_fiscale FROM persona p)) (Raise_application_error(-20000, 'Codice fiscale non valido'));2, CREATE OR REPLACE TRIGGER banchetto_trg BEFORE INSERT OR UPDATE OF cod_fiscale ON BANCHETTO FOR EACH ROW DECLARE Dummya CHAR(20); Dummyp CHAR(20); Invalid_cf EXCEPTION; CURSOR Dummy_cursora (cf CHAR) IS SELECT a.cod_fiscale FROM azienda a WHERE a.cod_fiscale = cf; CURSOR Dummy_cursorp (cf CHAR) IS SELECT p.cod_fiscale FROM persona p WHERE p.cod_fiscale = cf; BEGIN OPEN Dummy_cursora (:new.cod_fiscale); OPEN Dummy_cursorp (:new.cod_fiscale); FETCH Dummy_cursora INTO Dummya; FETCH Dummy_cursorp INTO Dummyp; IF Dummy_cursora%NOTFOUND THEN CLOSE Dummy_cursora; IF Dummy_cursorp%NOTFOUND THEN RAISE Invalid_cf; END IF; CLOSE Dummy_cursorp; END IF; EXCEPTION WHEN Invalid_cf THEN Raise_application_error(-20000, 'Codice fiscale non valido!'); CLOSE Dummy_cursorp; END;
http://www.pdown.net/download.asp?downid=1&id=117
我感觉,你的问题好像是在表里做一个触发器,当你向这个表插入数据时,由触发器来做逻辑判断,决定最终结果,如果是这个意思,触发器好像做不到
2,判断条件是什么?当前操作状态、字段值还是.......?
3,填入数据是填入当前表还是其他表?
1,
CREATE OR REPLACE TRIGGER banchetto_trg
BEFORE INSERT OR UPDATE OF cod_fiscale ON banchetto
REFERENCING NEW AS n
FOR EACH ROW
WHEN (n.cod_fiscale NOT IN (SELECT a.cod_fiscale FROM azienda a)
AND n.cod_fiscale NOT IN (SELECT p.cod_fiscale FROM persona p))
(Raise_application_error(-20000, 'Codice fiscale non valido'));2,
CREATE OR REPLACE TRIGGER banchetto_trg
BEFORE INSERT OR UPDATE OF cod_fiscale ON BANCHETTO
FOR EACH ROW
DECLARE
Dummya CHAR(20);
Dummyp CHAR(20);
Invalid_cf EXCEPTION;
CURSOR Dummy_cursora (cf CHAR) IS
SELECT a.cod_fiscale FROM azienda a
WHERE a.cod_fiscale = cf;
CURSOR Dummy_cursorp (cf CHAR) IS
SELECT p.cod_fiscale FROM persona p
WHERE p.cod_fiscale = cf;
BEGIN
OPEN Dummy_cursora (:new.cod_fiscale);
OPEN Dummy_cursorp (:new.cod_fiscale);
FETCH Dummy_cursora INTO Dummya;
FETCH Dummy_cursorp INTO Dummyp;
IF Dummy_cursora%NOTFOUND THEN
CLOSE Dummy_cursora;
IF Dummy_cursorp%NOTFOUND THEN
RAISE Invalid_cf;
END IF;
CLOSE Dummy_cursorp;
END IF;
EXCEPTION
WHEN Invalid_cf THEN
Raise_application_error(-20000, 'Codice fiscale non valido!');
CLOSE Dummy_cursorp;
END;