我的代码如下
CREATE TRIGGER SETSEASON AFTER UPDATE OR INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
UPDATE RecCenterMember
SET ClassSeason=(Select distinct(Season) From Class,Enrollment
where Class.id=new.Class_ID and ENROLLMENT.MEMBER_ID=NEW.MEMBER_ID)
where ID=new.member_ID;
END;
然后可以创建trigger
但是当我Insert Enrollment 表的时候
时却出错:ORA-04098: trigger 'SETSEASON' is invalid and failed re-validation帮我看看错在哪里了 谢谢了。
CREATE TRIGGER SETSEASON AFTER UPDATE OR INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
UPDATE RecCenterMember
SET ClassSeason=(Select distinct(Season) From Class,Enrollment
where Class.id=new.Class_ID and ENROLLMENT.MEMBER_ID=NEW.MEMBER_ID)
where ID=new.member_ID;
END;
然后可以创建trigger
但是当我Insert Enrollment 表的时候
时却出错:ORA-04098: trigger 'SETSEASON' is invalid and failed re-validation帮我看看错在哪里了 谢谢了。
还有,我想你Season应该是Class表中的吧,跟Enrollment表是通过哪个字段关联起来的,假设是Class.id
那么查询中你只需用:new关键字来获取Enrollment表中的值就行了,
CREATE TRIGGER SETSEASON
AFTER UPDATE OR INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
UPDATE RecCenterMember
SET ClassSeason=(Select distinct(Season) From Class where Class.id=:new.Class_ID AND ROWNUM=1)
where ID=:new.member_ID;
END;
SELECT DISTINCT (season)
FROM CLASS, enrollment
WHERE CLASS.ID = NEW.class_id
AND enrollment.member_id = NEW.member_id
CREATE TRIGGER SETSEASON before UPDATE OR INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
UPDATE RecCenterMember
SET ClassSeason=(Select distinct Season From Class where Class.id=:new.Class_ID and Class.MEMBER_ID=:new.MEMBER_ID)
where ID=:new.member_ID;
END;
FOR EACH ROW
BEGIN
UPDATE RecCenterMember
SET ClassSeason=(Select Season From Class
where Class.id=:new.Class_ID)
where ID=:new.member_ID;
END;
insert 引用触发器的触发表的字段 :new.col
update 引用触发器的触发表的字段 :old.col ,:new.col
delete 引用触发器的触发表的字段 :old.col