使用seq和Trigger在Oracle中自动递增-无效的SQL语句 [英] Autoincrement in oracle with seq and trigger - invalid sql statement
问题描述
我是oracle的新手,来自MySQL,我试图通过序列和触发器使autoincrement在Oracle中工作,以便每次执行插入操作时,它都会使字段增加一个.
I am new to oracle, comming from MySQL, and I am trying to get autoincrement to work in Oracle with a sequence and a trigger, so that it increments my field by one, each time I do an insert.
CREATE SEQUENCE proposals_seq MINVALUE 1
START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT
ON proposals
FOR EACH ROW
BEGIN
SELECT proposals_seq.nextval INTO :new.proposal_id FROM dual;
END;
但是当我运行脚本时,出现错误:
But when I run the script, I get the error:
Error code 900, SQL state 42000: ORA-00900: invalid SQL statement
如果我删除;"在select语句之后,我没有任何错误,直到尝试将数据插入表中,然后才出现此错误:
If I remove the ";" after the select statement, I get no error, until I try to insert data into the table, then I get this error:
INSERT INTO proposals (target_audience, line_of_business, activity_description, cost, comments, objectives_and_results)
VALUES ('test', 'test', 'test', 15, 'test', 'test');
Error code 4098, SQL state 42000: ORA-04098: trigger 'PROPOSALS_BEFORE_INSERT' is invalid and failed re-validation
我正在使用此版本的Oracle: Oracle Database 11g企业版11.2.0.1.0版-64位生产
I am using this version of Oracle: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
我找到的所有关于它的文章似乎都是这样做的,并且也在这里回答:
All the articles I have found about it, seems to be doing it like this and answers in here aswell: How to create id with AUTO_INCREMENT on Oracle?
可能是由于我的Oracle版本引起的吗?我还有另一种方法可以自动增加此值吗?还是我必须按照我的sql中的顺序手动增加?
Can it be because of my version of Oracle? Is there another way for me to autoincrement this? Or will I have to increment manually with the sequence in my sql?
我的桌子看起来像这样:
My table looks like this:
CREATE TABLE proposals (
proposal_id INT NOT NULL,
target_audience VARCHAR2(50) NOT NULL,
line_of_business VARCHAR2(50),
activity_description VARCHAR2(250) NOT NULL,
cost DECIMAL(19, 4) NOT NULL,
comments VARCHAR2(250),
objectives_and_results VARCHAR2(250),
PRIMARY KEY (proposal_id)
);
推荐答案
我怀疑问题是您的客户端工具正在读取每个分号作为命令的结尾,从而导致PL/SQL代码(需要分号作为语句终止符)被错误地传输到服务器.
I suspect the problem is that your client tool is reading every semicolon as the end of a command, causing PL/SQL code (which requires semicolons as statement terminators) to be incorrectly transmitted to the server.
删除分号时,该语句已正确发送到服务器,但是由于PL/SQL错误,最终会导致对象无效.
When you remove the semicolon, the statement is correctly sent to the server, but you end up with an invalid object because the PL/SQL is incorrect.
我在SQL Fiddle上重复了您的问题.然后,我将语句终止符更改为/
而不是;
,并更改了代码以使用斜杠执行每个语句,并且它的工作没有错误:
I duplicated your problem on SQL Fiddle. Then I change the statement terminator to /
instead of ;
and changed the code to use a slash to execute each statement, and it worked without error:
CREATE TABLE proposals (
proposal_id INT NOT NULL,
target_audience VARCHAR2(50) NOT NULL,
line_of_business VARCHAR2(50),
activity_description VARCHAR2(250) NOT NULL,
cost NUMBER(19, 4),
comments VARCHAR2(250),
objectives_and_results VARCHAR2(250),
PRIMARY KEY (proposal_id)
)
/
CREATE SEQUENCE proposals_seq MINVALUE 1
START WITH 1 INCREMENT BY 1 CACHE 10
/
CREATE OR REPLACE TRIGGER proposals_before_insert
BEFORE INSERT ON proposals FOR EACH ROW
BEGIN
select proposals_seq.nextval into :new.proposal_id from dual;
END;
/
这篇关于使用seq和Trigger在Oracle中自动递增-无效的SQL语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!