我建了三张表s,c,sc,我想建一个触发器,当s上增加一条记录时,在sc中为这个新增加的s#(学生)为每个课程添加一条记录,分数为0。比如在s中添加了一个学生test2,则触发器会在sc中添加三条记录,分别是(test2,1,0),(test2,2,0),(test2,3,0),相当于这个学生要选修c表中已经定义的所有课程,分数暂时为0
SQL> select * from s;S# SN SD SA
-- ------------------------------ ------------------------------------------------------------ --
1 nancy test 38
2 jane test 28
3 test test 28
4 test1 test2 48SQL> select * from c;C# CN
-- ------------------------------
1 math
2 language
3 chemicalSQL> select * from sc;S# C# G
-- -- --
2 3 60
2 1 89
1 2 80
2 2 70
4 5 0SQL> 请教一下这样的触发器该如何写,谢谢!
SQL> select * from s;S# SN SD SA
-- ------------------------------ ------------------------------------------------------------ --
1 nancy test 38
2 jane test 28
3 test test 28
4 test1 test2 48SQL> select * from c;C# CN
-- ------------------------------
1 math
2 language
3 chemicalSQL> select * from sc;S# C# G
-- -- --
2 3 60
2 1 89
1 2 80
2 2 70
4 5 0SQL> 请教一下这样的触发器该如何写,谢谢!
create or replace trigger ff
alter delete
on test
for each row
declare
begin
delete from test;
if sql%rowcount < 0 or sql%rowcount is null then
rais_replaction_err(-20004,"错误")
end if
end
create or replace trigger gg
alter insert
on test
for each row
declare
begin
if :old.names = :new.names then
raise_replaction_err(-2003,"编码重复");
end if
end
create or replace trigger hh
for update
on test
for each row
declare
begin
if updating then
if :old.names <> :new.names then
reaise_replaction_err(-2002,"关键字不能修改")
end if
end if
end
2 After insert or delete on s
3 for each row
4 DECLARE
5 iMax int
6 iIndex int
7
8 Begin
9 select max(c#) into iMax from c;
10 select min(c#) into iIndex from c;
11
12 IF INSERTING THEN
13 while iIndex <= iMax loop
14 INSERT INTO sc(s#,c#,G) values(:new.s#,iIndex,0) where iIndex in (select c# from c)
15 iIndex := iIndex +1;
16 end loop
17
18 ELSE
19 delete from sc where sc.s# not in (select s# from s);
20 END IF;
21 End s_test;
22 /Warning: Trigger created with compilation errorsSQL>
SQL> Create Or Replace Trigger s_test
2 After insert or delete on s
3 for each row
4 DECLARE
5 iMax int
6 iIndex int
7
8 Begin
9 select max(c#) into iMax from c;
10 select min(c#) into iIndex from c;
11
12 IF INSERTING THEN
13 while iIndex <= iMax loop
14 INSERT INTO sc(s#,c#,G) values(:new.s#,iIndex,0) where iIndex in (select c# from c);
15 iIndex := iIndex +1;
16 end loop
17
18 ELSE
19 delete from sc where sc.s# not in (select s# from s);
20 END IF;
21 End s_test;
22 /Warning: Trigger created with compilation errors
SQL> show error
Errors for TRIGGER NANCY.S_TEST:LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------------------------------------
3/5 PLS-00103: Encountered the symbol "IINDEX" when expecting one of the following: := . ( @ % ; not null range default character SQL>
5 iMax int
6 iIndex int
7
只需要插入S#的学号 因为往SC表插入的时候S#就成了固定的值了
所以后面的SQL语句可以写上
insert into sc select S#,C#,0 from C 基本上就可以了主要是你把SELECT出来的字段的名称必须AS 成SC对应的字段名称.
SQL> Create Or Replace Trigger s_test
2 After insert or delete on s
3 for each row
4 DECLARE
5 Begin
6 IF INSERTING THEN
7 insert into sc(s#,c#,g) select s.s#,c.c#,0 from s cross join c where s.s# = :new.s#;
8 ELSE
9 delete from sc where sc.s# not in (select s# from s);
10 END IF;
11 End s_test;
12 /Trigger createdSQL> select * from s;S# SN SD SA
-- ------------------------------ ------------------------------------------------------------ --
1 nancy test 38
2 jane test 28
3 test test 28
4 test1 test2 48SQL> select * from c;C# CN
-- ------------------------------
1 math
2 language
3 chemicalSQL> select * from sc;S# C# G
-- -- --
2 3 60
2 1 89
1 2 80
2 2 70
4 5 0SQL> insert into s values(5,'test3','test3',58);insert into s values(5,'test3','test3',58)ORA-04091: table NANCY.S is mutating, trigger/function may not see it
ORA-06512: at "NANCY.S_TEST", line 4
ORA-04088: error during execution of trigger 'NANCY.S_TEST'
SQL> Create Or Replace Trigger s_test
2 After insert or delete on s
3 for each row
4 DECLARE
5 iMax int ;
6 iIndex int;
7
8 Begin
9 select max(c#) into iMax from c;
10 select min(c#) into iIndex from c;
11
12 IF INSERTING THEN
13 while iIndex <= iMax loop
14 INSERT INTO sc(s#,c#,G) values(:new.s#,iIndex,0) where iIndex in (select c# from c);
15 iIndex := iIndex +1;
16 end loop ;
17
18 ELSE
19 delete from sc where sc.s# not in (select s# from s);
20 END IF;
21 End s_test;
22 /Warning: Trigger created with compilation errorsSQL> show error
Errors for TRIGGER NANCY.S_TEST:LINE/COL ERROR
-------- -------------------------------------------------
11/59 PL/SQL: ORA-00933: SQL command not properly ended
11/10 PL/SQL: SQL Statement ignored
SQL> Create Or Replace Trigger s_test
2 After insert or delete on s
3 for each row
4 DECLARE
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 Begin
7 IF INSERTING THEN
8 insert into sc(s#,c#,g) select :new.s#,c.c#,0 from c;
9 commit;
10 ELSE
11 delete from sc where sc.s# not in (select s# from s);
12 commit;
13 END IF;
14 End s_test;
15 /Trigger createdSQL> select * from s;S# SN SD SA
-- ------------------------------ ------------------------------------------------------------ --
1 nancy test 38
2 jane test 28
3 test test 28
4 test1 test2 48SQL> select * from sc;S# C# G
-- -- --
2 3 60
2 1 89
1 2 80
2 2 70
4 5 0SQL>
SQL> insert into s values(5,'test3','test3',58);1 row insertedSQL> select * from sc;S# C# G
-- -- --
2 3 60
2 1 89
1 2 80
2 2 70
4 5 0
5 1 0
5 2 0
5 3 08 rows selectedSQL> delete from s where s# = 5;1 row deletedSQL> select * from s;S# SN SD SA
-- ------------------------------ ------------------------------------------------------------ --
1 nancy test 38
2 jane test 28
3 test test 28
4 test1 test2 48SQL> select * rom sc;select * rom scORA-00923: FROM keyword not found where expectedSQL> select * from sc;S# C# G
-- -- --
2 3 60
2 1 89
1 2 80
2 2 70
4 5 0SQL>
`articles` for each row
update `users` set `nums`=`nums`+1
where `name`=NEW.author;
有两张表:
users 表:
`id` tinyint primary key,
`name` varchar(50),
`nums` tinyint
articles表:
`id` tinyint primary key,
`author` varchar(50)
记得users表的name字段的长度和articles表的author字段的长度相同。
以下是用命令行格式创建表
use test;//这句话的作用是使用数据库test
create table users(id tinyint primary key,name varchar(50),nums tinyint);
//创建表users
create table articles(id tinyint primary key,author varchar(50));
//创建表articles