遇到一个问题,不知道大家遇没遇到过,为了提高效率如下写,但是我试图提出来写几个function 来check old 跟 new 的值。却报错,感觉是when里面限制太多,请问有什么好的方法来检查这些update 的新旧值么?谢谢~create or replace trigger part_address_trg
AFTER INSERT OR UPDATE OF country,postal_area,address_type,geography_code_1,geography_code_2,geography_code_3 OR DELETE
ON address
FOR EACH ROW
WHEN(
NEW.country != OLD.country OR (NEW.country IS NULL AND OLD.country IS NOT NULL) OR (NEW.country IS NOT NULL AND OLD.country IS NULL) or
NEW.postal_area != OLD.postal_area OR (NEW.postal_area IS NULL AND OLD.postal_area IS NOT NULL) OR (NEW.postal_area IS NOT NULL AND OLD.postal_area IS NULL) or
NEW.address_type != OLD.address_type OR (NEW.address_type IS NULL AND OLD.address_type IS NOT NULL) OR (NEW.address_type IS NOT NULL AND OLD.address_type IS NULL) or
NEW.geography_code_1 != OLD.geography_code_1 OR (NEW.geography_code_1 IS NULL AND OLD.geography_code_1 IS NOT NULL) OR (NEW.geography_code_1 IS NOT NULL AND OLD.geography_code_1 IS NULL) or
NEW.geography_code_2 != OLD.geography_code_2 OR (NEW.geography_code_2 IS NULL AND OLD.geography_code_2 IS NOT NULL) OR (NEW.geography_code_2 IS NOT NULL AND OLD.geography_code_2 IS NULL) or
NEW.geography_code_3 != OLD.geography_code_3 OR (NEW.geography_code_3 IS NULL AND OLD.geography_code_3 IS NOT NULL) OR (NEW.geography_code_3 IS NOT NULL AND OLD.geography_code_3 IS NULL)
)
DECLARE
ad_address_id address.address_id%TYPE;
BEGIN
.................
END
/
AFTER INSERT OR UPDATE OF country,postal_area,address_type,geography_code_1,geography_code_2,geography_code_3 OR DELETE
ON address
FOR EACH ROW
WHEN(
NEW.country != OLD.country OR (NEW.country IS NULL AND OLD.country IS NOT NULL) OR (NEW.country IS NOT NULL AND OLD.country IS NULL) or
NEW.postal_area != OLD.postal_area OR (NEW.postal_area IS NULL AND OLD.postal_area IS NOT NULL) OR (NEW.postal_area IS NOT NULL AND OLD.postal_area IS NULL) or
NEW.address_type != OLD.address_type OR (NEW.address_type IS NULL AND OLD.address_type IS NOT NULL) OR (NEW.address_type IS NOT NULL AND OLD.address_type IS NULL) or
NEW.geography_code_1 != OLD.geography_code_1 OR (NEW.geography_code_1 IS NULL AND OLD.geography_code_1 IS NOT NULL) OR (NEW.geography_code_1 IS NOT NULL AND OLD.geography_code_1 IS NULL) or
NEW.geography_code_2 != OLD.geography_code_2 OR (NEW.geography_code_2 IS NULL AND OLD.geography_code_2 IS NOT NULL) OR (NEW.geography_code_2 IS NOT NULL AND OLD.geography_code_2 IS NULL) or
NEW.geography_code_3 != OLD.geography_code_3 OR (NEW.geography_code_3 IS NULL AND OLD.geography_code_3 IS NOT NULL) OR (NEW.geography_code_3 IS NOT NULL AND OLD.geography_code_3 IS NULL)
)
DECLARE
ad_address_id address.address_id%TYPE;
BEGIN
.................
END
/
解决方案 »
- 求2个date字段,相差多少个月?
- 求oracle增量备份的方法
- oracle中如何建立索引和使用索引(请举例说明)非常感谢
- grant授权问题
- 初学sql*plus,问个简单的问题!来看下呗^_^
- 游标问题,有问题,没解决
- 在客户端pl/sql 无法处理服务器名的问题:
- ORA-12154 :TNS:无法处理服务名
- person_list_ := person_list_ || person_information.name|| ' <' || person_information.identity|| '>' || field_separator_;这句话是
- oracle dbms_lob.append 遇到奇怪问题
- 求教一个odbc连接oracle数据库的问题。
- 急急急求!!!!windows下oracle远程访问Mysql的问题
比如你已经
AFTER INSERT OR UPDATE OF country,postal_area,address_type,geography_code_1,geography_code_2,geography_code_3
就没有必要在when里使用
NEW.geography_code_1 != OLD.geography_code_1
建议使用updating来修改一下:
create or replace trigger part_address_trg
AFTER INSERT OR UPDATE OF country,postal_area,address_type,geography_code_1,geography_code_2,geography_code_3 OR DELETE
ON address
FOR EACH ROW
DECLARE
ad_address_id address.address_id%TYPE;
BEGIN
if updating('COUNTRY') then
----
end if;
if updating('POST_AREA') then
----
end if;
if updating('ADDRESS_TYPE') then
----
end if;
END
/