1、确保序列TAB_SEQUENCE存在 2、确保表USERS存在,且含有USERID子段 创建trigger的语句如下:CREATE OR REPLACE TRIGGER USERS_BIR BEFORE INSERT ON USERS FOR EACH ROW DECLARE TEMPNUM NUMBER; BEGIN SELECT TAB_SEQUENCE.NEXTVAL INTO TEMPNUM FROM DUAL; :NEW.USERID := 'Usr' || LPAD(TEMPNUM, 7, '0'); END USERS_BIR;
:new userid 要写成 :new.userid
up像这个需求不用trigger也可以直接做的哟insert into users values('user'||tab_sequence.nextval, .......);
再帮忙看下,结果如下:SQL> CREATE OR REPLACE TRIGGER USERS_TG 2 BEFORE INSERT ON TESTTB 3 FOR EACH ROW 4 DECLARE 5 TEMPNUM NUMBER; 6 BEGIN 7 SELECT T1_SEQUENCE.NEXTVAL 8 INTO TEMPNUM 9 FROM DUAL; 10 :NEW.ID := 'Usr' || LPAD(TEMPNUM, 11 7, 12 '0'); 13 END USERS_TG; 14 . SQL> COMMIT;提交完成。SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS;未选定行SQL> INSERT INTO TESTTB(USERNAME) VALUES('TEST1'); INSERT INTO TESTTB(USERNAME) VALUES('TEST1') * ERROR 位于第 1 行: ORA-01400: 无法将 NULL 插入 ("XWW"."TESTTB"."ID") SQL>
在建立完Trigger后,使用: SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS; 查询当前用户的Trigger表中,就没有Trigger,不知道是咋回事?
:new userid := 'Usr' || lpad(tempnum,7,'0');
这样你就可以明白NEW的一些涵义了,当然这只是一部分理解,更深的需要你自己看看NEW与OLD的定义规则了!
2、确保表USERS存在,且含有USERID子段
创建trigger的语句如下:CREATE OR REPLACE TRIGGER USERS_BIR
BEFORE INSERT ON USERS
FOR EACH ROW
DECLARE
TEMPNUM NUMBER;
BEGIN
SELECT TAB_SEQUENCE.NEXTVAL INTO TEMPNUM
FROM DUAL;
:NEW.USERID := 'Usr' || LPAD(TEMPNUM,
7,
'0');
END USERS_BIR;
up像这个需求不用trigger也可以直接做的哟insert into users values('user'||tab_sequence.nextval, .......);
2 BEFORE INSERT ON TESTTB
3 FOR EACH ROW
4 DECLARE
5 TEMPNUM NUMBER;
6 BEGIN
7 SELECT T1_SEQUENCE.NEXTVAL
8 INTO TEMPNUM
9 FROM DUAL;
10 :NEW.ID := 'Usr' || LPAD(TEMPNUM,
11 7,
12 '0');
13 END USERS_TG;
14 .
SQL> COMMIT;提交完成。SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS;未选定行SQL> INSERT INTO TESTTB(USERNAME) VALUES('TEST1');
INSERT INTO TESTTB(USERNAME) VALUES('TEST1')
*
ERROR 位于第 1 行:
ORA-01400: 无法将 NULL 插入 ("XWW"."TESTTB"."ID")
SQL>
SQL> SELECT TRIGGER_NAME FROM USER_TRIGGERS;
查询当前用户的Trigger表中,就没有Trigger,不知道是咋回事?
你都没指明触发器的名称,怎么查?
你上面编写的触发器名是USERS_TG你的查询语句至少是这样的:
select USERS_TG from USER_TRIGGERS;
这是用来查询前用户的触发器表中所有的TRIGGER_NAME字段的数据的.