触发器问题

[wfdoohla]

#表
CREATE TABLE films2 (
code CHARACTER(5),
title CHARACTER VARYING(40),
did DECIMAL(3),
date_prod DATE,
kind CHAR(10)
)

#创建触发器
CREATE SEQUENCE SDID
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999
/
CREATE TRIGGER FILMS2_BEFORE_INSERT_ROW
BEFORE INSERT ON films2
FOR EACH ROW
Begin
SELECT SDID.NEXTVAL FROM DUAL;
End;
/

执行
insert into films2 ( CODE, TITLE, DID ) values ( 'ss14','tss1s4',190 )

错误为
trigger 'SYSTEM.FILMS2_BEFORE_INSERT_ROW' is invalid and failed re-validation

[welyngj]

CREATE TRIGGER FILMS2_BEFORE_INSERT_ROW
BEFORE INSERT ON films2
FOR EACH ROW
Begin
SELECT SDID.NEXTVAL FROM DUAL;
End;
/
要实现什么？

[welyngj]

SELECT SDID.NEXTVAL FROM DUAL; --错误

[sunliwen780502]

SELECT一句应该为SELECT SDID.NEXTVAL INTO :NEW.id FROM DUAL;
其中的id 为字段

[wfdoohla]

我修改为
CREATE TRIGGER FILMS2_BEFORE_INSERT_ROW
BEFORE INSERT ON films2
FOR EACH ROW
Begin
SELECT SDID.NEXTVAL INTO :NEW.DID FROM DUAL;
End;

DID 是 films2 的字段吧?
还是一样的错误.

我在
Begin
End;
中不写语句也会出错

[LGQDUCKY]

改为这样
create or replace FILMS2_BEFORE_INSERT_ROW
before insert on films2 for each row
declare
next_id number;
begin
select SDID.NEXTVAL into next_id from dual;
:new.id := next_id;
end;

[dlmaomao]

问题：
1、你建的触发器做什么用？
2、语句insert into films2 ( CODE, TITLE, DID ) values ( 'ss14','tss1s4',190 )
在那执行？sql> 还是程序中？
3、1和2之间有何联系？

[anbeel]

这样呢？
Begin
NULL;
End;

[wfdoohla]

CREATE TRIGGER FILMS2_BEFORE_INSERT_ROW
BEFORE INSERT ON films2
FOR EACH ROW
Begin
NULL;
End;

还是提示
trigger 'SYSTEM.FILMS2_BEFORE_INSERT_ROW' is invalid and failed re-validation

目的是在 films2 中增加自增主键 DID

[ttoc]

pl/sql 中的select语句好像必须有into，begin和end之间必须有语句

SQL> CREATE TABLE films2 (
2 code CHARACTER(5),
3 title CHARACTER VARYING(40),
4 did DECIMAL(3),
5 date_prod DATE,
6 kind CHAR(10)
7 );

表已创建。

SQL>
SQL> CREATE SEQUENCE SDID
2 INCREMENT BY 1
3 START WITH 1
4 MAXVALUE 99999999
5 /

序列已创建。

SQL>
SQL> CREATE TRIGGER FILMS2_BEFORE_INSERT_ROW
2 BEFORE INSERT ON films2
3 FOR EACH ROW
4 Begin
5 null;
6 End;
7 /

触发器已创建

[wfdoohla]

错误

ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS

SQL> connect system /as sysdba
Enter password:
Connected.
SQL> CREATE TRIGGER aaaa_BEFORE_INSERT_ROW
BEFORE INSERT ON aaaa
FOR EACH ROW
Begin
SELECT FFFF1.NEXTVAL FROM DUAL;
End;
/
2 3 4 5 6 7 CREATE TRIGGER aaaa_BEFORE_INSERT_ROW
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS

[wfdoohla]

谢谢大家

[wfdoohla]

有兴趣到这些地方领分
这样创建触发器 那错了? 在线等（wfdoohla）
如何建立自增主键（wfdoohla）