Warning: compiled but with compilation errors Errors for TRIGGER USER_TRILINE/COL ERROR -------- ----------------------------------------------------------------- 3/53 PLS-00049: bad bind variable 'NEW.COL2' 5/29 PLS-00049: bad bind variable 'NEW.COL4'
create or replace trigger user_tri before insert or update or delete on ol_user_t for each row begin if inserting then insert into users values (ol_bbs_user_s.nextval,:new.LoginName, :new.Password,'1.gif',sysdate,1,1,50,1); elsif updating then update users set uPass = :new.Password where uName = :new.LoginName; else delete from users where uName = :new.LoginName; end if; end; 你col2,col4,col3,ORACLE当然不认。
SQL> create or replace trigger user_tri 2 before insert or update or delete on ol_user_t 3 for each row 4 begin 5 if inserting then 6 insert into users values (ol_bbs_user_s.nextval,:new.LoginName, :new.Pa ssword,'1.gif',sysdate,1,1,50,1); 7 elsif updating then 8 update users set uPass = :new.Password where uName = :new.LoginName; 9 else 10 delete from users where uName = :new.LoginName; 11 end if; 12 end; 13 /触发器已创建SQL> show error 没有错误。 SQL> insert into ol_user_t values(25,'Allen','Iverson','Allen Iverson','学生','0 606110001','06IT');已创建 1 行。SQL> commit 2 ;提交完成。SQL> select * from ol_user_t; USERID LOGINNAME USERNAME PASSWORD TYPE ---------- -------------------- ------------ ---------------- ---------------- ID GRADE ---------------- -------------------- 25 Allen Iverson Allen Iverson 学生 0606110001 06IT SQL> select * from users; USERID UNAME UPASS HEAD ---------- -------------------- -------------------- -------------------- REGTIME GENDER UPOINT UGRADE ISADMIN -------------- ---------- ---------- ---------- ---------- 1 Allen Allen Iverson 1.gif 03-2月 -10 1 1 50 1 SQL>
你users表中就没有col3,col4列,当然报错了。
改一下trigger代码:create or replace trigger user_tri before insert or update or delete on ol_user_t for each row begin if inserting then insert into users values (ol_bbs_user_s.nextval,:new.username, :new.password,'1.gif',sysdate,1,1,50,1); elsif updating then update users set upass = :new.password where uName = :new.LoginName; else delete from users where uName = :new.LoginName; end if; end;
col2,col3,col4到底要表示什麽? 應該是這樣,我猜的 :create or replace trigger user_tri before insert or update or delete on ol_user_t for each row begin if inserting then insert into users values (ol_bbs_user_s.nextval,:new.loginName, :new.userName,'1.gif',sysdate,1,1,50,1); elsif updating then update users set uPass = :new.Password where uName = :new.LoginName; else delete from users where uName = :new.LoginName; end if; end;
col2,col4,col3用列名代替create trigger user_tri before insert or update or delete on ol_user_t for each row begin if inserting then insert into users values (ol_bbs_user_s.nextval, :new.LoginName, new.Password, '1.gif', sysdate, 1, 1, 50, 1); elsif updating then update users set uPass = :new.Password where uName = :new.LoginName; else delete from users where uName = :new.LoginName; end if; end;
create or replace trigger user_tri before insert or update or delete on ol_user_t for each row begin if inserting then insert into users values (1,:new.LoginName, :new.Password ,'1.gif',sysdate,1,1,50,1); elsif updating then update users set uPass = :new.Password where uName = :new.LoginName; else delete from users where uName = :new.LoginName; end if; end; 把col改成对应的字段名,通过, 插入数据没有问题。
create or replace trigger user_tri before insert or update or delete on ol_user_t for each row begin if inserting then insert into users values (1,:new.LoginName, :new.Password ,'1.gif',sysdate,1,1,50,1); elsif updating then update users set uPass = :new.Password where uName = :new.LoginName; else delete from users where uName = :new.LoginName; end if; end; 插入数据,没有问题。
SQL> create table ol_user_t 2 ( 3 UserId number not null, 4 LoginName varchar2(20) not null, 5 UserName varchar2(12) not null, 6 Password varchar2(16) not null, 7 Type varchar2(16) not null, 8 ID varchar2(16) not null, 9 Grade varchar2(20) , 10 primary key (UserId) 11 ) ;表已创建。SQL> create table users 2 ( 3 UserId number not null, 4 uName varchar2(20) not null, 5 uPass varchar2(20) not null, 6 head varchar2(20) not null, 7 regTime date not null, 8 gender number not null, 9 uPoint number not null, 10 uGrade number not null, 11 isAdmin number not null 12 );表已创建。SQL> create sequence ol_bbs_user_s start with 88 increment by 1;序列已创建。SQL> create or replace trigger user_tri 2 before insert or update or delete on ol_user_t 3 for each row 4 begin 5 if inserting then 6 insert into users values (ol_bbs_user_s.nextval,:new.LoginName, :new.Pa ssword,'1.gif',sysdate,1,1,50,1); 7 elsif updating then 8 update users set uPass = :new.Password where uName = :new.LoginName; 9 else 10 delete from users where uName = :new.LoginName; 11 end if; 12 end; 13 /触发器已创建SQL> show error 没有错误。 SQL> SQL> select * from ol_user_t 2 ;未选定行SQL> select * from users;未选定行SQL> insert into ol_user_t values(25,'Allen','Iverson','Allen Iverson','学生','0 606110001','06IT');已创建 1 行。SQL> commit;提交完成。SQL> select * from ol_user_t; USERID LOGINNAME USERNAME PASSWORD TYPE ---------- -------------------- ------------ ---------------- ---------------- ID GRADE ---------------- -------------------- 25 Allen Iverson Allen Iverson 学生 0606110001 06IT SQL> select * from users; USERID UNAME UPASS HEAD ---------- -------------------- -------------------- -------------------- REGTIME GENDER UPOINT UGRADE ISADMIN -------------- ---------- ---------- ---------- ---------- 88 Allen Allen Iverson 1.gif 03-2月 -10 1 1 50 1 SQL>
try itConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as billing SQL> SQL> drop table ol_user_t;Table droppedSQL> create table ol_user_t 2 ( 3 UserId number not null, 4 LoginName varchar2(20) not null, 5 UserName varchar2(12) not null, 6 Password varchar2(16) not null, 7 Type varchar2(16) not null, 8 ID varchar2(16) not null, 9 Grade varchar2(20) , 10 primary key (UserId) 11 ) ;Table createdSQL> drop table users;Table droppedSQL> create table users 2 ( 3 UserId number not null, 4 uName varchar2(20) not null, 5 uPass varchar2(20) not null, 6 head varchar2(20) not null, 7 regTime date not null, 8 gender number not null, 9 uPoint number not null, 10 uGrade number not null, 11 isAdmin number not null 12 );Table createdSQL> create or replace trigger user_tri 2 before insert or update or delete on ol_user_t 3 for each row 4 begin 5 if inserting then 6 insert into users 7 values 8 (100, 9 :new.LoginName, 10 :new.Password, 11 '1.gif', 12 sysdate, 13 1, 14 1, 15 50, 16 1); 17 elsif updating then 18 update users set uPass = :new.Password where uName = :new.LoginName; 19 else 20 delete from users where uName = :new.LoginName; 21 end if; 22 end; 23 /Trigger createdSQL> insert into ol_user_t values(25,'Allen','Iverson','Allen Iverson','学生','0606110001','06IT');1 row insertedSQL>
Errors for TRIGGER USER_TRILINE/COL ERROR
-------- -----------------------------------------------------------------
3/53 PLS-00049: bad bind variable 'NEW.COL2'
5/29 PLS-00049: bad bind variable 'NEW.COL4'
before insert or update or delete on ol_user_t
for each row
begin
if inserting then
insert into users values (ol_bbs_user_s.nextval,:new.LoginName, :new.Password,'1.gif',sysdate,1,1,50,1);
elsif updating then
update users set uPass = :new.Password where uName = :new.LoginName;
else
delete from users where uName = :new.LoginName;
end if;
end;
你col2,col4,col3,ORACLE当然不认。
2 before insert or update or delete on ol_user_t
3 for each row
4 begin
5 if inserting then
6 insert into users values (ol_bbs_user_s.nextval,:new.LoginName, :new.Pa
ssword,'1.gif',sysdate,1,1,50,1);
7 elsif updating then
8 update users set uPass = :new.Password where uName = :new.LoginName;
9 else
10 delete from users where uName = :new.LoginName;
11 end if;
12 end;
13 /触发器已创建SQL> show error
没有错误。
SQL> insert into ol_user_t values(25,'Allen','Iverson','Allen Iverson','学生','0
606110001','06IT');已创建 1 行。SQL> commit
2 ;提交完成。SQL> select * from ol_user_t; USERID LOGINNAME USERNAME PASSWORD TYPE
---------- -------------------- ------------ ---------------- ----------------
ID GRADE
---------------- --------------------
25 Allen Iverson Allen Iverson 学生
0606110001 06IT
SQL> select * from users; USERID UNAME UPASS HEAD
---------- -------------------- -------------------- --------------------
REGTIME GENDER UPOINT UGRADE ISADMIN
-------------- ---------- ---------- ---------- ----------
1 Allen Allen Iverson 1.gif
03-2月 -10 1 1 50 1
SQL>
before insert or update or delete on ol_user_t
for each row
begin
if inserting then
insert into users values (ol_bbs_user_s.nextval,:new.username, :new.password,'1.gif',sysdate,1,1,50,1);
elsif updating then
update users set upass = :new.password where uName = :new.LoginName;
else
delete from users where uName = :new.LoginName;
end if;
end;
應該是這樣,我猜的 :create or replace trigger user_tri
before insert or update or delete on ol_user_t
for each row
begin
if inserting then
insert into users values (ol_bbs_user_s.nextval,:new.loginName, :new.userName,'1.gif',sysdate,1,1,50,1);
elsif updating then
update users set uPass = :new.Password where uName = :new.LoginName;
else
delete from users where uName = :new.LoginName;
end if;
end;
before insert or update or delete on ol_user_t
for each row
begin
if inserting then
insert into users
values
(ol_bbs_user_s.nextval,
:new.LoginName,
new.Password,
'1.gif',
sysdate,
1,
1,
50,
1);
elsif updating then
update users set uPass = :new.Password where uName = :new.LoginName;
else
delete from users where uName = :new.LoginName;
end if;
end;
before insert or update or delete on ol_user_t
for each row
begin
if inserting then
insert into users values (1,:new.LoginName, :new.Password ,'1.gif',sysdate,1,1,50,1);
elsif updating then
update users set uPass = :new.Password where uName = :new.LoginName;
else
delete from users where uName = :new.LoginName;
end if;
end;
把col改成对应的字段名,通过,
插入数据没有问题。
before insert or update or delete on ol_user_t
for each row
begin
if inserting then
insert into users values (1,:new.LoginName, :new.Password ,'1.gif',sysdate,1,1,50,1);
elsif updating then
update users set uPass = :new.Password where uName = :new.LoginName;
else
delete from users where uName = :new.LoginName;
end if;
end;
插入数据,没有问题。
2 (
3 UserId number not null,
4 LoginName varchar2(20) not null,
5 UserName varchar2(12) not null,
6 Password varchar2(16) not null,
7 Type varchar2(16) not null,
8 ID varchar2(16) not null,
9 Grade varchar2(20) ,
10 primary key (UserId)
11 ) ;表已创建。SQL> create table users
2 (
3 UserId number not null,
4 uName varchar2(20) not null,
5 uPass varchar2(20) not null,
6 head varchar2(20) not null,
7 regTime date not null,
8 gender number not null,
9 uPoint number not null,
10 uGrade number not null,
11 isAdmin number not null
12 );表已创建。SQL> create sequence ol_bbs_user_s start with 88 increment by 1;序列已创建。SQL> create or replace trigger user_tri
2 before insert or update or delete on ol_user_t
3 for each row
4 begin
5 if inserting then
6 insert into users values (ol_bbs_user_s.nextval,:new.LoginName, :new.Pa
ssword,'1.gif',sysdate,1,1,50,1);
7 elsif updating then
8 update users set uPass = :new.Password where uName = :new.LoginName;
9 else
10 delete from users where uName = :new.LoginName;
11 end if;
12 end;
13 /触发器已创建SQL> show error
没有错误。
SQL>
SQL> select * from ol_user_t
2 ;未选定行SQL> select * from users;未选定行SQL> insert into ol_user_t values(25,'Allen','Iverson','Allen Iverson','学生','0
606110001','06IT');已创建 1 行。SQL> commit;提交完成。SQL> select * from ol_user_t; USERID LOGINNAME USERNAME PASSWORD TYPE
---------- -------------------- ------------ ---------------- ----------------
ID GRADE
---------------- --------------------
25 Allen Iverson Allen Iverson 学生
0606110001 06IT
SQL> select * from users; USERID UNAME UPASS HEAD
---------- -------------------- -------------------- --------------------
REGTIME GENDER UPOINT UGRADE ISADMIN
-------------- ---------- ---------- ---------- ----------
88 Allen Allen Iverson 1.gif
03-2月 -10 1 1 50 1
SQL>
Connected as billing
SQL>
SQL> drop table ol_user_t;Table droppedSQL> create table ol_user_t
2 (
3 UserId number not null,
4 LoginName varchar2(20) not null,
5 UserName varchar2(12) not null,
6 Password varchar2(16) not null,
7 Type varchar2(16) not null,
8 ID varchar2(16) not null,
9 Grade varchar2(20) ,
10 primary key (UserId)
11 ) ;Table createdSQL> drop table users;Table droppedSQL> create table users
2 (
3 UserId number not null,
4 uName varchar2(20) not null,
5 uPass varchar2(20) not null,
6 head varchar2(20) not null,
7 regTime date not null,
8 gender number not null,
9 uPoint number not null,
10 uGrade number not null,
11 isAdmin number not null
12 );Table createdSQL> create or replace trigger user_tri
2 before insert or update or delete on ol_user_t
3 for each row
4 begin
5 if inserting then
6 insert into users
7 values
8 (100,
9 :new.LoginName,
10 :new.Password,
11 '1.gif',
12 sysdate,
13 1,
14 1,
15 50,
16 1);
17 elsif updating then
18 update users set uPass = :new.Password where uName = :new.LoginName;
19 else
20 delete from users where uName = :new.LoginName;
21 end if;
22 end;
23 /Trigger createdSQL> insert into ol_user_t values(25,'Allen','Iverson','Allen Iverson','学生','0606110001','06IT');1 row insertedSQL>