--之前代码写完之后,触发器创建成功了,但是没测试 。。然后测试的时候出问题了
-- 执行update和delete操作的时候,报错invalid number
-- 后来发现时因为本地id类型是number,远程是varchar2,而且远程表里边有varchar2类型的id数据
-- 所以where id的时候会报错,,
--解决方式如下:声明一个目标类型的变量来接数据,然后再过去比较。问题解决
CREATE OR REPLACE TRIGGER U_MyTrigger AFTER UPDATE ON TYPE FOR EACH ROW
DECLARE
old_id VARCHAR2(36);
BEGIN
old_id := :OLD.ID;
if :OLD.P_ID IS NULL THEN
UPDATE 目标库.DIC_GROUP
SET ID=:NEW.ID,DIC_NAME=:NEW.TYPE_NAME,DIC_CODE=:NEW.B_CODE
WHERE ID=old_id;
ELSIF :OLD.P_ID IS NOT NULL THEN
UPDATE 目标库.DIC_GROUP_ITEMS
SET ITEM_NAME=:NEW.TYPE_NAME,DIC_CODE=:NEW.B_CODE,PARENT_ID=:NEW.P_ID
WHERE ID=old_id;
end if;
END;
CREATE OR REPLACE TRIGGER D_MyTrigger AFTER DELETE ON TYPE FOR EACH ROW
DECLARE
old_id VARCHAR2(36);
BEGIN
old_id := :OLD.ID;
if :OLD.P_ID IS NULL THEN
DELETE from 目标库._DIC_GROUP
WHERE ID=old_id;
elsIf :OLD.P_ID IS NOT NULL THEN
DELETE from 目标库.DIC_GROUP_ITEMS
WHERE ID=old_id;
end if;
END;