我想在表t_jobplan插入一条记录时触发触发器,使这个表里的一个字段WPSEQID(它是一个自增长的6位码)加入当前日期yyyyMMdd型的,谢谢各位了,本人写了一点,不会了!!!谢谢!
create or replace trigger
after insert on t_jobplan
for each row
declare
-- local variables here
begin
update t_jobplan set wpseqid=:new where wpseqid=(SELECT SYSDATE FROM DUAL)
end ;
create or replace trigger
after insert on t_jobplan
for each row
declare
-- local variables here
begin
update t_jobplan set wpseqid=:new where wpseqid=(SELECT SYSDATE FROM DUAL)
end ;
如下就可以了:
CREATE OR REPLACE TRIGGER TRIG_TEST
AFTER INSERT ON T_JOBPLAN
FOR EACH ROW IS
BEGIN
:NEW.WPSEQID := TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'));
END;
/
====================================================LZ这种做法好象欠妥哟,最好另加一字段来存放当前日期。
自增字段WPSEQID一般作记录的主键用。
--------------------------------------------什么意思?用的Sequence使它自动增长(1,2,3,4)?还是固定的6位数增长(100001,100002)
AFTER INSERT ON T_JOBPLAN
FOR EACH ROW IS
BEGIN
update t_jobplan set wpseqid=:old.wpseqid ||sydate;
END;
BEFORE INSERT ON T_JOBPLAN
FOR EACH ROW
DECLARE
l_num NUMBER;
BEGIN
SELECT seq_3.nextval INTO l_num FROM dual;
:NEW.WPSEQID := TO_NUMBER(l_num||TO_CHAR(SYSDATE, 'yyyymmdd'));
END;
/CREATE TABLE t_jobplan (a NUMBER,wpseqid NUMBER);
CREATE SEQUENCE seq_3;INSERT INTO t_jobplan VALUES(1,2);
INSERT INTO t_jobplan VALUES(2,2);
INSERT INTO t_jobplan VALUES(3,2);
SELECT * FROM t_jobplan;
输出:
A WPSEQID
1 120081008
2 220081008
3 320081008
BEFORE INSERT ON T_JOBPLAN
FOR EACH ROW
DECLARE
l_num NUMBER;
BEGIN
SELECT seq_3.nextval INTO l_num FROM dual;
:NEW.WPSEQID := TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd')||lpad(l_num,6,'0'));
END;
/DROP TABLE t_jobplan;
DROP SEQUENCE seq_3;
CREATE TABLE t_jobplan (a NUMBER,wpseqid NUMBER);
CREATE SEQUENCE seq_3;INSERT INTO t_jobplan VALUES(1,2);
INSERT INTO t_jobplan VALUES(2,2);
INSERT INTO t_jobplan VALUES(3,2);
SELECT * FROM t_jobplan;
输出:
A WPSEQID
1 20081008000007
2 20081008000008
3 20081008000009
AFTER INSERT ON T_JOBPLAN
FOR EACH ROW IS
BEGIN
update t_jobplan set wpseqid=to_date(sydate,'yyyy-mm-dd')||:old.wpseqid ;
END;