表cal_user
user_id
NUMBER (10,0) NOT NULL,
time_zone VARCHAR2 (255) NOT NULL,
表cal_digest
owner_id
NUMBER (10,0) NOT NULL,
time_zone NUMBER(2,1) NOT NULL,
user_id 是primary key owner_id是foreign key，
要求插入cal_digest表时自动从cal_user表查询time_zone并插入。写TIGGER如下：
CREATE OR REPLACE TRIGGER CAL_DIGEST_trigger
AFTERINSERT ON CAL_DIGEST
FOREACH ROW
BEGIN
UPDATE CAL_DIGEST
SET CAL_DIGEST.time_zone = (SELECT time_zone
FROM cal_user
WHERE :new.owner_id =user_id );
END CAL_DIGEST_trigger;
编译通过，但是我向cal_digest表插入记录时报错
ORA-04091: table CALENDAR.CAL_DIGEST is mutating, trigger/function may not see it
ORA-06512: at "CALENDAR.CAL_DIGEST_TRIGGER", line 6
ORA-04088: error during execution of trigger 'CALENDAR.CAL_DIGEST_TRIGGER'
怎么解决？
|
