nextval can not be used in assignment " = "I think that's another reason .
Using Sequence Values You can use CURRVAL and NEXTVAL in these places:the SELECT list of a SELECT statement that is not contained in a subquery, snapshot or view the SELECT list of a subquery in an INSERT statement the VALUES clause of an INSERT statement the SET clause of an UPDATE statement You cannot use CURRVAL and NEXTVAL in these places:a subquery in a DELETE, SELECT, or UPDATE statement a view's query or snapshot's query a SELECT statement with the DISTINCT operator a SELECT statement with a GROUP BY or ORDER BY clause a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator the WHERE clause of a SELECT statement DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement the condition of a CHECK constraint
create or replace trigger bef_ins_t_test before insert on t_test referencing old as old new as new for each row begin select t_test_id.nextval into new.id from dual; end; / 试试吧
new.id = t_test_id.nextval; 错了,应该加个:在NEW的前面
试了,两种方法都不行,不会是oracle数据库有问题吧? 1.:new.id = t_test_id.nextval; SQL> create or replace trigger bef_ins_t_test 2 before insert on t_test 3 referencing old as old new as new 4 for each row 5 begin 6 :new.id = t_test_id.nextval; 7 --select t_test_id.nextval into new.id from dual; 8 end; 9 /警告: 创建的触发器带有编译错误。2.select t_test_id.nextval into new.id from dual; SQL> create or replace trigger bef_ins_t_test 2 before insert on t_test 3 referencing old as old new as new 4 for each row 5 begin 6 --:new.id = t_test_id.nextval; 7 select t_test_id.nextval into new.id from dual; 8 end; 9 /警告: 创建的触发器带有编译错误。 我该怎么办???
奇怪,这样吧 create or replace trigger bef_ins_t_test before insert on t_test for each row begin select t_test_id.nextval into :new.id from dual; end; /
借宝地一用,我一问一下! referencing old as old new as new 起什么作用,触发器中 :new 和 :old 在什么情况下用,请高手指点!
是一个代替意思,请参考以下例子吧。CREATE OR REPLACE TRIGGER GenerateStudentID BEFORE INSERT OR UPDATE ON students REFERENCING new AS new_student --在语句部分,以后就用new_student代替new,作用是等同 FOR EACH ROW BEGIN SELECT student_sequence.nextval INTO :new_student.ID FROM dual; END GenerateStudentID; /
可以在语句部分控制,没有影响,当然你删除了记录后,在表中的id是不会连续的,请注意。..... if inserting then ... elsif updatint then .... else .... end if;
1 create or replace trigger bef_ins_t_test 2 before insert on t_test 3 referencing old as old new as new 4 for each row 5 begin 6 --:new.id := t_test_id.nextval; 7 select t_test_id.nextval into :new.id from dual; 8* end; SQL> / 这个测试通过了!
create sequence S_A_EventID increment by 1 start with 1 maxvalue 999999999; CREATE OR REPLACE TRIGGER T_A_EventID BEFORE INSERT ON AnnounceEvent REFERENCING OLD AS old NEW AS new FOR EACH ROW BEGIN IF :new.EventID IS NULL THEN SELECT S_A_EventID.NEXTVAL INTO :new.EventID FROM dual; END IF; END; / 其中S_A_EventID是SEQUENCE名,T_A_EventID是TRIGGER名。 AnnounceEvent是表名。
You can use CURRVAL and NEXTVAL in these places:the SELECT list of a SELECT statement that is not contained in a subquery, snapshot or view
the SELECT list of a subquery in an INSERT statement
the VALUES clause of an INSERT statement
the SET clause of an UPDATE statement
You cannot use CURRVAL and NEXTVAL in these places:a subquery in a DELETE, SELECT, or UPDATE statement
a view's query or snapshot's query
a SELECT statement with the DISTINCT operator
a SELECT statement with a GROUP BY or ORDER BY clause
a SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator
the WHERE clause of a SELECT statement
DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
the condition of a CHECK constraint
before insert on t_test
referencing old as old new as new
for each row
begin
select t_test_id.nextval into new.id from dual;
end;
/
试试吧
错了,应该加个:在NEW的前面
1.:new.id = t_test_id.nextval;
SQL> create or replace trigger bef_ins_t_test
2 before insert on t_test
3 referencing old as old new as new
4 for each row
5 begin
6 :new.id = t_test_id.nextval;
7 --select t_test_id.nextval into new.id from dual;
8 end;
9 /警告: 创建的触发器带有编译错误。2.select t_test_id.nextval into new.id from dual;
SQL> create or replace trigger bef_ins_t_test
2 before insert on t_test
3 referencing old as old new as new
4 for each row
5 begin
6 --:new.id = t_test_id.nextval;
7 select t_test_id.nextval into new.id from dual;
8 end;
9 /警告: 创建的触发器带有编译错误。
我该怎么办???
create or replace trigger bef_ins_t_test
before insert on t_test
for each row
begin
select t_test_id.nextval into :new.id from dual;
end;
/
referencing old as old new as new 起什么作用,触发器中 :new 和 :old 在什么情况下用,请高手指点!
譬如:insert,update,delete;再或者操作未成功,再操作的时候,id会不会乱跳,……
还有没有其他的解决办法(序列+程序实现除外)??
BEFORE INSERT OR UPDATE ON students
REFERENCING new AS new_student --在语句部分,以后就用new_student代替new,作用是等同
FOR EACH ROW
BEGIN
SELECT student_sequence.nextval
INTO :new_student.ID
FROM dual;
END GenerateStudentID;
/
if inserting then
...
elsif updatint then
....
else
....
end if;
2 before insert on t_test
3 referencing old as old new as new
4 for each row
5 begin
6 --:new.id := t_test_id.nextval;
7 select t_test_id.nextval into :new.id from dual;
8* end;
SQL> /
这个测试通过了!
CREATE OR REPLACE TRIGGER T_A_EventID
BEFORE INSERT ON AnnounceEvent
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
BEGIN
IF :new.EventID IS NULL THEN
SELECT S_A_EventID.NEXTVAL
INTO :new.EventID
FROM dual;
END IF;
END;
/
其中S_A_EventID是SEQUENCE名,T_A_EventID是TRIGGER名。
AnnounceEvent是表名。
我不是怕辛苦啊,事实上所有的表我都已经用这个方法建好了。只是回想有点不甘心啊,好象跟计算机在抢饭碗一样:)
有没有其他方法,譬如:能否在建trigger的时候将表名改成变量名,再在需要用到的时候引用该trigger?
高手们,提点建议吧