我浏览了前面的答案,创建了一个伪外键来引用Netbeans 8.1中两个数据库之间的表。这是我想出的代码,
DELIMITER //
CREATE OR REPLACE TRIGGER conf_track_FK
AFTER INSERT OR UPDATE on S26994437.track@FIT5148B
FOR EACH ROW
BEGIN
IF EXISTS(select * from inserted I where not exists (select * from
S1234567.conference@FIT5148A A where I.conf_id=A.conf_id))
RAISE_APPLICATION_ERROR(-20001,'Violation of pseudo-foreign key.');
ROLLBACK;
END IF;
END;
/
然而,我遇到了以下错误:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
) with and or group having intersect minus start union where
connect
PLS-00103: Encountered the symbol "ROLLBACK" when expecting one of the following:
:= . ( % ;
The symbol ":=" was substituted for "ROLLBACK" to continue.
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
创建触发器dbo。MyTableTrigger打开dbo。MyTable,插入后更新像开始
如果不存在(从OtherB.dbo.TableName中选择PK,其中PK在(从插入的FK中选择)BEGIN--在此处处理参考错误结束
结束
试试下面的代码片段。希望能有所帮助。而且,任何像COMMIT/ROLLBACK这样的事务都不能放在触发器内部,除非它是一个自治事务,但这也不是一个好主意,因为父事务被隐藏在触发器事务中。因此,即使Parent事务失败,Automnomos事务也会完成。
CREATE OR REPLACE TRIGGER conf_track_FK AFTER
INSERT OR
UPDATE ON S26994437.track@FIT5148B --Remove DB link as it cant be used in Trigger
FOR EACH ROW
DECLARE
lv_cnt PLS_INTEGER;
BEGIN
SELECT COUNT(1)
INTO lv_cnt
FROM inserted I
WHERE NOT EXISTS
(SELECT 1 FROM S1234567.conference@FIT5148A A WHERE I.conf_id=A.conf_id
);
IF lv_cnt > 0 THEN
RAISE_APPLICATION_ERROR(-20001,'Violation of pseudo-foreign key.');
END IF;
END;
/