如果A 表任意字段修改, 都更新B B任意修改,都更新A 的话, 我觉得需要在A 和B 表中增加一个字段,判断数据更新的来源如果不是全字段都更新的话可以设置触发器只对某一个或几个字段进行同步更新的if updating(col1,col2) then
create or replace trigger info_to_stu before update on pr_student_info for each row declare v_student pe_student%rowtype; pragma autonomous_transaction; begin select * into v_student from pe_student t1 where t1.fk_student_info_id = :new.id; if v_student.birthday = :new.birthday and v_student.flag_gender = :new.gender and v_student.card_type = :new.CARD_TYPE and v_student.card_no = :new.CARD_NO and v_student.FOLK = :new.FORK and v_student.PROVINCE = :new.PROVINCE and v_student.CITY = :new.CITY and v_student.ZZMM = :new.ZZMM and v_student.PRE_XUELI = :new.XUELI and v_student.MARRIAGE = :new.MARRIAGE and v_student.EMAIL = :new.EMAIL and v_student.OCCUPATION = :new.OCCUPATION and v_student.WORKPLACE = :new.WORKPLACE and v_student.ZIP = :new.ZIP and v_student.ADDRESS = :new.ADDRESS and v_student.PHONE = :new.PHONE and v_student. MOBILE = :new.MOBILEPHONE and v_student. PRE_GRADUATE_SCHOOL = :new.GRADUATE_SCHOOL and v_student. PRE_GRADUATE_SCHOOL_CODE = :new.GRADUATE_SCHOOL_CODE and v_student. PRE_GRADUATE_DATE = :new.GRADUATE_YEAR and v_student. PRE_GRADUATE_CODE = :new.GRADUATE_CODE and v_student.XUEZHI = :new.XUEZHI and v_student.PHOTO_LINK_GRADUATION = :new.PHOTO_LINK and v_student.LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE then null; else update pe_student t set birthday = :new.birthday, flag_gender = :new.gender, CARD_TYPE = :new.card_type, CARD_NO = :new.card_no, FOLK = :new.FORK, PROVINCE = :new.PROVINCE, CITY = :new.CITY, ZZMM = :new.ZZMM, PRE_XUELI = :new.XUELI, MARRIAGE = :new.MARRIAGE, EMAIL = :new.EMAIL, OCCUPATION = :new.OCCUPATION, WORKPLACE = :new.WORKPLACE, ZIP = :new.ZIP, ADDRESS = :new.ADDRESS, PHONE = :new.PHONE, MOBILE = :new.MOBILEPHONE, PRE_GRADUATE_SCHOOL = :new.GRADUATE_SCHOOL, PRE_GRADUATE_SCHOOL_CODE = :new.GRADUATE_SCHOOL_CODE, PRE_GRADUATE_DATE = :new.GRADUATE_YEAR, PRE_GRADUATE_CODE = :new.GRADUATE_CODE, XUEZHI = :new.XUEZHI, PHOTO_LINK_GRADUATION = :new.PHOTO_LINK, LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE where t.fk_student_info_id = :new.id; COMMIT;
end if; end; 这时一个触发器
create or replace trigger stu_to_info before update of birthday, flag_gender, CARD_TYPE, CARD_NO, FOLK, PROVINCE, CITY, ZZMM, PRE_XUELI, MARRIAGE, EMAIL, OCCUPATION, WORKPLACE, ZIP, ADDRESS, PHONE, MOBILE, PRE_GRADUATE_SCHOOL, PRE_GRADUATE_SCHOOL_CODE, PRE_GRADUATE_DATE, PRE_GRADUATE_CODE, XUEZHI, PHOTO_LINK_GRADUATION, LAST_INFO_UPDATE_DATE on pe_student for each row declare v_student_info pr_student_info%rowtype; pragma autonomous_transaction; begin select * into v_student_info from pr_student_info t1 where t1.id = :new.fk_student_info_id; if v_student_info.birthday = :new.birthday and v_student_info.gender = :new.flag_gender and v_student_info.card_type = :new.CARD_TYPE and v_student_info.card_no = :new.CARD_NO and v_student_info.FORK = :new.FOLK and v_student_info.PROVINCE = :new.PROVINCE and v_student_info.CITY = :new.CITY and v_student_info.ZZMM = :new.ZZMM and v_student_info.XUELI = :new.PRE_XUELI and v_student_info.MARRIAGE = :new.MARRIAGE and v_student_info.EMAIL = :new.EMAIL and v_student_info.OCCUPATION = :new.OCCUPATION and v_student_info.WORKPLACE = :new.WORKPLACE and v_student_info.ZIP = :new.ZIP and v_student_info.ADDRESS = :new.ADDRESS and v_student_info.PHONE = :new.PHONE and v_student_info.MOBILEPHONE = :new.MOBILE and v_student_info.GRADUATE_SCHOOL = :new.PRE_GRADUATE_SCHOOL and v_student_info.GRADUATE_SCHOOL_CODE = :new.PRE_GRADUATE_SCHOOL_CODE and v_student_info.GRADUATE_YEAR = :new.PRE_GRADUATE_DATE and v_student_info.GRADUATE_CODE = :new.PRE_GRADUATE_CODE and v_student_info.XUEZHI = :new.XUEZHI and v_student_info.PHOTO_LINK = :new.PHOTO_LINK_GRADUATION and v_student_info.LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE then null; else update pr_student_info t set birthday = :new.birthday, gender = :new.flag_gender, card_type = :new.CARD_TYPE, card_no = :new.CARD_NO, FORK = :new.FOLK, PROVINCE = :new.PROVINCE, CITY = :new.CITY, ZZMM = :new.ZZMM, XUELI = :new.PRE_XUELI, MARRIAGE = :new.MARRIAGE, EMAIL = :new.EMAIL, OCCUPATION = :new.OCCUPATION, WORKPLACE = :new.WORKPLACE, ZIP = :new.ZIP, ADDRESS = :new.ADDRESS, PHONE = :new.PHONE, MOBILEPHONE = :new.MOBILE, GRADUATE_SCHOOL = :new.PRE_GRADUATE_SCHOOL, GRADUATE_SCHOOL_CODE = :new.PRE_GRADUATE_SCHOOL_CODE, GRADUATE_YEAR = :new.PRE_GRADUATE_DATE, GRADUATE_CODE = :new.PRE_GRADUATE_CODE, XUEZHI = :new.XUEZHI, PHOTO_LINK = :new.PHOTO_LINK_GRADUATION, LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE where t.id = :new.fk_student_info_id; COMMIT;
end if;end; 这时另一个,更新其中一个表就会报死锁
我寫了一個,供大家參考,需要加一個package存放一個是否返回更新原表的標誌-- 測試數據 DROP TABLE A; DROP TABLE B; CREATE TABLE A (a1 NUMBER PRIMARY KEY, a2 VARCHAR2(20)); CREATE TABLE B (b1 NUMBER PRIMARY KEY, b2 VARCHAR2(20));INSERT INTO A (a1, a2) VALUES (1, 'ABC'); INSERT INTO B (b1, b2) VALUES (1, 'xyz'); INSERT INTO A (a1, a2) VALUES (2, 'CDE'); INSERT INTO B (b1, b2) VALUES (2, 'OPQ'); COMMIT;-- 存放標誌的package CREATE OR REPLACE PACKAGE PKG_TRG_A_B AS g_no_update_back_a BOOLEAN; g_no_update_back_b BOOLEAN; END PKG_TRG_A_B;-- 觸發器a CREATE OR REPLACE TRIGGER trg_a AFTER UPDATE ON A FOR EACH ROW BEGIN pkg_trg_a_b.g_no_update_back_a := TRUE;
IF pkg_trg_a_b.g_no_update_back_b != TRUE THEN UPDATE B SET b2 = :new.a2 WHERE b1 = :new.a1; ELSE pkg_trg_a_b.g_no_update_back_a := FALSE; END IF; pkg_trg_a_b.g_no_update_back_b := FALSE; END;-- 觸發器b CREATE OR REPLACE TRIGGER trg_b AFTER UPDATE ON B FOR EACH ROW BEGIN pkg_trg_a_b.g_no_update_back_b := TRUE;
IF pkg_trg_a_b.g_no_update_back_a != TRUE THEN UPDATE A SET a2 = :new.b2 WHERE a1 = :new.b1; ELSE pkg_trg_a_b.g_no_update_back_b := FALSE; END IF; pkg_trg_a_b.g_no_update_back_a := FALSE; END;-- 測試語句 UPDATE A SET a2 = 'OOO';SELECT * FROM A; A1 A2 ---------- ------------------------------ 1 OOO 2 OOO SELECT * FROM B; B1 B2 ---------- ------------------------------ 1 OOO 2 OOO
如果A 表任意字段修改, 都更新B
B任意修改,都更新A 的话,
我觉得需要在A 和B 表中增加一个字段,判断数据更新的来源如果不是全字段都更新的话可以设置触发器只对某一个或几个字段进行同步更新的if updating(col1,col2) then
before update on pr_student_info
for each row
declare
v_student pe_student%rowtype;
pragma autonomous_transaction;
begin
select *
into v_student
from pe_student t1
where t1.fk_student_info_id = :new.id;
if v_student.birthday = :new.birthday and
v_student.flag_gender = :new.gender and
v_student.card_type = :new.CARD_TYPE and
v_student.card_no = :new.CARD_NO and v_student.FOLK = :new.FORK and
v_student.PROVINCE = :new.PROVINCE and v_student.CITY = :new.CITY and
v_student.ZZMM = :new.ZZMM and v_student.PRE_XUELI = :new.XUELI and
v_student.MARRIAGE = :new.MARRIAGE and v_student.EMAIL = :new.EMAIL and
v_student.OCCUPATION = :new.OCCUPATION and
v_student.WORKPLACE = :new.WORKPLACE and v_student.ZIP = :new.ZIP and
v_student.ADDRESS = :new.ADDRESS and v_student.PHONE = :new.PHONE and
v_student. MOBILE = :new.MOBILEPHONE and v_student.
PRE_GRADUATE_SCHOOL = :new.GRADUATE_SCHOOL and v_student.
PRE_GRADUATE_SCHOOL_CODE = :new.GRADUATE_SCHOOL_CODE and v_student.
PRE_GRADUATE_DATE = :new.GRADUATE_YEAR and v_student.
PRE_GRADUATE_CODE = :new.GRADUATE_CODE and
v_student.XUEZHI = :new.XUEZHI and
v_student.PHOTO_LINK_GRADUATION = :new.PHOTO_LINK and
v_student.LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE
then
null;
else
update pe_student t set birthday = :new.birthday,
flag_gender = :new.gender,
CARD_TYPE = :new.card_type,
CARD_NO = :new.card_no,
FOLK = :new.FORK,
PROVINCE = :new.PROVINCE,
CITY = :new.CITY,
ZZMM = :new.ZZMM,
PRE_XUELI = :new.XUELI,
MARRIAGE = :new.MARRIAGE,
EMAIL = :new.EMAIL,
OCCUPATION = :new.OCCUPATION,
WORKPLACE = :new.WORKPLACE,
ZIP = :new.ZIP,
ADDRESS = :new.ADDRESS,
PHONE = :new.PHONE,
MOBILE = :new.MOBILEPHONE,
PRE_GRADUATE_SCHOOL = :new.GRADUATE_SCHOOL,
PRE_GRADUATE_SCHOOL_CODE = :new.GRADUATE_SCHOOL_CODE,
PRE_GRADUATE_DATE = :new.GRADUATE_YEAR,
PRE_GRADUATE_CODE = :new.GRADUATE_CODE,
XUEZHI = :new.XUEZHI,
PHOTO_LINK_GRADUATION = :new.PHOTO_LINK,
LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE
where t.fk_student_info_id = :new.id;
COMMIT;
end if;
end;
这时一个触发器
before update of birthday, flag_gender, CARD_TYPE, CARD_NO, FOLK, PROVINCE, CITY, ZZMM, PRE_XUELI, MARRIAGE, EMAIL, OCCUPATION, WORKPLACE, ZIP, ADDRESS, PHONE, MOBILE, PRE_GRADUATE_SCHOOL, PRE_GRADUATE_SCHOOL_CODE, PRE_GRADUATE_DATE, PRE_GRADUATE_CODE, XUEZHI, PHOTO_LINK_GRADUATION, LAST_INFO_UPDATE_DATE
on pe_student
for each row
declare
v_student_info pr_student_info%rowtype;
pragma autonomous_transaction;
begin
select *
into v_student_info
from pr_student_info t1
where t1.id = :new.fk_student_info_id;
if v_student_info.birthday = :new.birthday and
v_student_info.gender = :new.flag_gender and
v_student_info.card_type = :new.CARD_TYPE and
v_student_info.card_no = :new.CARD_NO and
v_student_info.FORK = :new.FOLK and
v_student_info.PROVINCE = :new.PROVINCE and
v_student_info.CITY = :new.CITY and v_student_info.ZZMM = :new.ZZMM and
v_student_info.XUELI = :new.PRE_XUELI and
v_student_info.MARRIAGE = :new.MARRIAGE and
v_student_info.EMAIL = :new.EMAIL and
v_student_info.OCCUPATION = :new.OCCUPATION and
v_student_info.WORKPLACE = :new.WORKPLACE and
v_student_info.ZIP = :new.ZIP and
v_student_info.ADDRESS = :new.ADDRESS and
v_student_info.PHONE = :new.PHONE and
v_student_info.MOBILEPHONE = :new.MOBILE and
v_student_info.GRADUATE_SCHOOL = :new.PRE_GRADUATE_SCHOOL and
v_student_info.GRADUATE_SCHOOL_CODE = :new.PRE_GRADUATE_SCHOOL_CODE and
v_student_info.GRADUATE_YEAR = :new.PRE_GRADUATE_DATE and
v_student_info.GRADUATE_CODE = :new.PRE_GRADUATE_CODE and
v_student_info.XUEZHI = :new.XUEZHI and
v_student_info.PHOTO_LINK = :new.PHOTO_LINK_GRADUATION and
v_student_info.LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE
then
null;
else
update pr_student_info t
set birthday = :new.birthday,
gender = :new.flag_gender,
card_type = :new.CARD_TYPE,
card_no = :new.CARD_NO,
FORK = :new.FOLK,
PROVINCE = :new.PROVINCE,
CITY = :new.CITY,
ZZMM = :new.ZZMM,
XUELI = :new.PRE_XUELI,
MARRIAGE = :new.MARRIAGE,
EMAIL = :new.EMAIL,
OCCUPATION = :new.OCCUPATION,
WORKPLACE = :new.WORKPLACE,
ZIP = :new.ZIP,
ADDRESS = :new.ADDRESS,
PHONE = :new.PHONE,
MOBILEPHONE = :new.MOBILE,
GRADUATE_SCHOOL = :new.PRE_GRADUATE_SCHOOL,
GRADUATE_SCHOOL_CODE = :new.PRE_GRADUATE_SCHOOL_CODE,
GRADUATE_YEAR = :new.PRE_GRADUATE_DATE,
GRADUATE_CODE = :new.PRE_GRADUATE_CODE,
XUEZHI = :new.XUEZHI,
PHOTO_LINK = :new.PHOTO_LINK_GRADUATION,
LAST_INFO_UPDATE_DATE = :new.LAST_INFO_UPDATE_DATE
where t.id = :new.fk_student_info_id;
COMMIT;
end if;end;
这时另一个,更新其中一个表就会报死锁
DROP TABLE A;
DROP TABLE B;
CREATE TABLE A (a1 NUMBER PRIMARY KEY, a2 VARCHAR2(20));
CREATE TABLE B (b1 NUMBER PRIMARY KEY, b2 VARCHAR2(20));INSERT INTO A (a1, a2) VALUES (1, 'ABC');
INSERT INTO B (b1, b2) VALUES (1, 'xyz');
INSERT INTO A (a1, a2) VALUES (2, 'CDE');
INSERT INTO B (b1, b2) VALUES (2, 'OPQ');
COMMIT;-- 存放標誌的package
CREATE OR REPLACE PACKAGE PKG_TRG_A_B
AS
g_no_update_back_a BOOLEAN;
g_no_update_back_b BOOLEAN;
END PKG_TRG_A_B;-- 觸發器a
CREATE OR REPLACE TRIGGER trg_a
AFTER UPDATE ON A
FOR EACH ROW
BEGIN
pkg_trg_a_b.g_no_update_back_a := TRUE;
IF pkg_trg_a_b.g_no_update_back_b != TRUE THEN
UPDATE B SET b2 = :new.a2 WHERE b1 = :new.a1;
ELSE
pkg_trg_a_b.g_no_update_back_a := FALSE;
END IF;
pkg_trg_a_b.g_no_update_back_b := FALSE;
END;-- 觸發器b
CREATE OR REPLACE TRIGGER trg_b
AFTER UPDATE ON B
FOR EACH ROW
BEGIN
pkg_trg_a_b.g_no_update_back_b := TRUE;
IF pkg_trg_a_b.g_no_update_back_a != TRUE THEN
UPDATE A SET a2 = :new.b2 WHERE a1 = :new.b1;
ELSE
pkg_trg_a_b.g_no_update_back_b := FALSE;
END IF;
pkg_trg_a_b.g_no_update_back_a := FALSE;
END;-- 測試語句
UPDATE A SET a2 = 'OOO';SELECT * FROM A;
A1 A2
---------- ------------------------------
1 OOO
2 OOO SELECT * FROM B;
B1 B2
---------- ------------------------------
1 OOO
2 OOO