CREATE OR REPLACE TRIGGER TRG_APP_MSTR BEFORE UPDATE OR INSERT OF APP_MSTR REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW BEGIN IF (:NEW.app_sale_conf='0' OR :OLD.app_sale_conf='0') AND (:NEW.app_fi_conf='0' OR :OLD.app_fi_conf='0') THEN :NEW.app_nbr:='LS' || TO_CHAR(RENBANSEQ.NEXTVAL,'FM99000000'); END IF; END;
CREATE OR REPLACE SEQUENCE seq_app_mstr START WITH 1 INCREMENT BY 1;CREATE OR REPLACE TRIGGER trg_app_mstr BEFORE UPDATE ON app_mstr WHEN(:NEW.app_sale_conf='0' AND :NEW.app_fi_conf='0') FOR EACH ROW DECLARE num NUMBER; BEGIN SELECT seq_app_mstr.NEXTVAL INTO num FROM dual; :NEW.app_nbr:='LS'||TO_CHAR(num,'FM99000000'); END;
create table test (tname varchar2(20),status1 char, status2 char, seq char(8)); create sequence s_test start with 1 increment by 1;insert into test values ('Task1', '1','1','LS' || to_char(S_TEST.nextval,'fm099999')); insert into test values ('Task2', '1','1','LS' || to_char(S_TEST.nextval,'fm099999')); commit;select * from test;TNAME STATUS1 STATUS2 SEQ ------------------------------ ------- ------- ------------ Task1 1 1 LS000001 Task2 1 1 LS000002 create or replace trigger trg_test before update on test for each row declare vseq number; begin if :new.status1 = '0' and :new.status2 = '0' then select s_test.nextval into vseq from dual; :new.seq := 'LS' || to_char(vseq,'fm099999'); end if; end;update test set status1 = '0' , status2 = '0' where tname = 'Task1'; commit;select * from test;TNAME STATUS1 STATUS2 SEQ ------------------------------ ------- ------- ------------ Task1 0 0 LS000003 Task2 1 1 LS000002
SQL> create table app_mstr(app_sale_conf varchar2(10),app_fi_conf varchar2(10),app_nbr varchar2(8)) 2 /表已创建。SQL> create sequence seq_app 2 start with 1 3 increment by 1 4 /序列已创建。SQL> edi 已写入 file afiedt.buf 1 create or replace trigger tri_appup before insert or update on app_mstr for each row 2 when(new.app_sale_conf='0' and new.app_fi_conf='0') 3 begin 4 select 'LS'||lpad(seq_app.nextval,6,'0') into :new.app_nbr from dual; 5* end; SQL> /触发器已创建SQL> insert into app_mstr values('0','0','1') 2 /已创建 1 行。SQL> commit 2 /提交完成。SQL> select * from app_mstr 2 /APP_SALE_C APP_FI_CON APP_NBR ---------- ---------- -------- 0 0 LS000001
BEFORE UPDATE OR INSERT OF APP_MSTR
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
IF (:NEW.app_sale_conf='0' OR :OLD.app_sale_conf='0') AND (:NEW.app_fi_conf='0' OR :OLD.app_fi_conf='0') THEN
:NEW.app_nbr:='LS' || TO_CHAR(RENBANSEQ.NEXTVAL,'FM99000000');
END IF;
END;
CREATE OR REPLACE SEQUENCE seq_app_mstr START WITH 1 INCREMENT BY 1;CREATE OR REPLACE TRIGGER trg_app_mstr
BEFORE UPDATE ON app_mstr
WHEN(:NEW.app_sale_conf='0' AND :NEW.app_fi_conf='0')
FOR EACH ROW
DECLARE
num NUMBER;
BEGIN
SELECT seq_app_mstr.NEXTVAL INTO num FROM dual;
:NEW.app_nbr:='LS'||TO_CHAR(num,'FM99000000');
END;
create table test (tname varchar2(20),status1 char, status2 char, seq char(8));
create sequence s_test start with 1 increment by 1;insert into test values ('Task1', '1','1','LS' || to_char(S_TEST.nextval,'fm099999'));
insert into test values ('Task2', '1','1','LS' || to_char(S_TEST.nextval,'fm099999'));
commit;select * from test;TNAME STATUS1 STATUS2 SEQ
------------------------------ ------- ------- ------------
Task1 1 1 LS000001
Task2 1 1 LS000002 create or replace trigger trg_test
before update on test
for each row
declare
vseq number;
begin
if :new.status1 = '0' and :new.status2 = '0' then
select s_test.nextval into vseq from dual;
:new.seq := 'LS' || to_char(vseq,'fm099999');
end if;
end;update test set status1 = '0' , status2 = '0' where tname = 'Task1';
commit;select * from test;TNAME STATUS1 STATUS2 SEQ
------------------------------ ------- ------- ------------
Task1 0 0 LS000003
Task2 1 1 LS000002
SQL> create table app_mstr(app_sale_conf varchar2(10),app_fi_conf varchar2(10),app_nbr varchar2(8)) 2 /表已创建。SQL> create sequence seq_app
2 start with 1
3 increment by 1
4 /序列已创建。SQL> edi
已写入 file afiedt.buf 1 create or replace trigger tri_appup before insert or update on app_mstr for each row
2 when(new.app_sale_conf='0' and new.app_fi_conf='0')
3 begin
4 select 'LS'||lpad(seq_app.nextval,6,'0') into :new.app_nbr from dual;
5* end;
SQL> /触发器已创建SQL> insert into app_mstr values('0','0','1')
2 /已创建 1 行。SQL> commit
2 /提交完成。SQL> select * from app_mstr
2 /APP_SALE_C APP_FI_CON APP_NBR
---------- ---------- --------
0 0 LS000001