creat table textx
(
gh varchar2(10),--工号
gzrq date null, --工作日期
gl NUMBER(16) --工龄
)写个触发器
新增或修改员工工作日期时 自动计算工龄 Extract(year from sysdate)-Extract(year from gzrq)+1
(
gh varchar2(10),--工号
gzrq date null, --工作日期
gl NUMBER(16) --工龄
)写个触发器
新增或修改员工工作日期时 自动计算工龄 Extract(year from sysdate)-Extract(year from gzrq)+1
before update or insert
on textx
for each row
begin
:new.gl := extract(year from sysdate) - extract(year from :new.gzrq) + 1;
end;
/
Wrote file afiedt.buf 1 create or replace trigger tri_textx
2 before insert or update on textx
3 for each row
4 begin
5 select extract(year from sysdate) - extract(year from :new.gzrq) into :new.gl from dual;
6* end;
SQL> /Trigger created.SQL> insert into textx(gh, gzrq) values(1, to_date('1993/1/1', 'yyyy/mm/dd')) ;1 row created.SQL> select * from textx;GH GZRQ GL
---------- ------------------- ----------
1 1993-01-01 00:00:00 16SQL>
(
gh varchar2(10),--工号
gzrq date null, --工作日期
gl NUMBER(16) --工龄
) 写个触发器
新增或修改员工工作日期时 自动计算工龄 Extract(year from sysdate)-Extract(year from gzrq)+1 -----------------------------create or replace trigger textx_trig
before insert or update
on ods.textx
for each row
begin
:new.gl := Extract(year from sysdate)-Extract(year from :new.gzrq)+1;
end;-------
insert into ods.textx(gh,gzrq) values(12,sysdate-1);
commit;