如何在Oracle SQL中使用触发器显示AGE?

Question

我有两个不同的表，我在其中创建了表 1

CREATE TABLE  "STUD_PERS" 
(    "ENROLL" NUMBER NOT NULL ENABLE, 
"NAME" VARCHAR2(30), 
"BRANCH" VARCHAR2(30), 
"YEAR" NUMBER, 
"DATE0FBIRTH" DATE NOT NULL ENABLE, 
CONSTRAINT "STUD_PERS_PK" PRIMARY KEY ("ENROLL") ENABLE
)

表1

表2

CREATE TABLE  "SCHO" 
(    "ENROLL" NUMBER NOT NULL ENABLE, 
"ANN_INC" NUMBER, 
"AGE" NUMBER, 
CONSTRAINT "SCHO_PK" PRIMARY KEY ("ENROLL") ENABLE
)

表2

我想编写一个触发器，通过使用表 1 中的出生日期在表 2 中显示 AGE

Answer 1

1(据我所知，您的问题和评论在更新发生时都需要一个触发器，对吧？因此，如果有人更新表1，则触发器必须更新表2上的年龄，如果是，请尝试以下代码：

---- TRIGGER will UPDATE the data of the column AGE in table2 whenever DATE0FBIRTH column has been UPDATED and COMMITTED:
CREATE OR REPLACE TRIGGER scho_age 
AFTER UPDATE OF DATE0FBIRTH 
ON  STUD_PERS
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
UPDATE SCHO SET AGE = (EXTRACT (YEAR FROM SYSDATE) - EXTRACT (YEAR FROM :NEW.DATE0FBIRTH)) WHERE ENROLL= :OLD.ENROLL;
END;

2( 或者，如果您只需要显示，则为 table1 创建一个视图并从那里查询 AGE，而无需复制数据：

SELECT (EXTRACT (YEAR FROM SYSDATE) - EXTRACT (YEAR from DATE0FBIRTH)) AGE from table1;