Oracle 中插入后触发插入 sysdate
我尝试使用以下命令，但显然它是无效的 SQL：
CREATE OR REPLACE TRIGGER QUESTION_DATE BEFORE INSERT ON QUESTION
FOR EACH ROW
BEGIN
INSERT INTO QUESTION(CREATED_TIMESTAMP)
VALUES (SYSDATE);
END;
到目前为止，问题表看起来像这样：
CREATE TABLE QUESTION
(
QUESTION_ID INTEGER not null,
LATEST_QUESTION INTEGER not null,
CREATED_USER_ID INTEGER not null,
CREATED_TIMESTAMP TIMESTAMP not null,
CONSTRAINT PK_QUESTION PRIMARY KEY (QUESTION_ID)
);
CREATE SEQUENCE QUESTION_ID_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE NOORDER;
CREATE TRIGGER QUESTION_INSERT BEFORE INSERT ON QUESTION
FOR EACH ROW
BEGIN
SELECT QUESTION_ID_SEQ.nextval
INTO :new.QUESTION_ID
FROM dual;
END;
我正在使用 Toad for Oracle V9.0.1.8 如果相关的话
I tried using the following but apparently it's invalid SQL:
CREATE OR REPLACE TRIGGER QUESTION_DATE BEFORE INSERT ON QUESTION
FOR EACH ROW
BEGIN
INSERT INTO QUESTION(CREATED_TIMESTAMP)
VALUES (SYSDATE);
END;
The Question table looks like this so far:
CREATE TABLE QUESTION
(
QUESTION_ID INTEGER not null,
LATEST_QUESTION INTEGER not null,
CREATED_USER_ID INTEGER not null,
CREATED_TIMESTAMP TIMESTAMP not null,
CONSTRAINT PK_QUESTION PRIMARY KEY (QUESTION_ID)
);
CREATE SEQUENCE QUESTION_ID_SEQ INCREMENT BY 1 START WITH 1 NOCYCLE NOCACHE NOORDER;
CREATE TRIGGER QUESTION_INSERT BEFORE INSERT ON QUESTION
FOR EACH ROW
BEGIN
SELECT QUESTION_ID_SEQ.nextval
INTO :new.QUESTION_ID
FROM dual;
END;
I'm using Toad for Oracle V9.0.1.8 if that's relevant
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不要使用触发器在 Oracle 中设置默认值。相反，请在列上使用“DEFAULT”。
这是一个示例列
Dont use a trigger to set a default value in Oracle. Instead, use "DEFAULT" on the column.
Here is an exmple column
我认为您可能想要这样：
您的触发器尝试将另一行插入到 QUESTION 中，这将触发触发器并...
I think you probably want this:
Your trigger tries to insert another row into QUESTION, which would fire the trigger and...
:new.created_timestamp := sysdate
而不是插入。
插入已经发生，无需再次执行。
您还可以将 sysdate 设置为该列的默认值，但这将允许在插入语句中覆盖该值。
:new.created_timestamp := sysdate
Instead of insert.
The insert is already occurring, no need to do it again.
You could also make sysdate the default for the column, but that would allow for the value to be overridden in the insert statement.