实验10 触发器

1、创建一个删除触发器aaa。要求删除teacher表中某一个教师的信息时，自动删除course表中该教师所教授的课程。并测试结果。

delimiter $$
create trigger aaa after delete on teacher
for each row
begin
delete from course where tno=old.tno;
end
$$
delimiter ;

触发:
delete from teacher where tname='赵天应';
set foreign_key_checks=0;
select * from teacher;
select * from course;

2、创建一个修改触发器bbb。要求修改dept表中的系号时，自动更新student表中相关的deptno的值。并测试结果。

drop trigger if exists bbb;
delimiter $$
create trigger bbb after update on dept
for each row
begin
update student set deptno=new.deptno where deptno=old.deptno;
end
$$
delimiter ;

触发:
update dept set deptno=11 where deptno=10;
select * from dept;
select * from student;

3、建立一个触发器，当向sc表中添加数据时，如果添加的数据与student表中的数据不匹配(没有对应的学号)，则将此数据删除。

drop trigger if exists sc_insert;
delimiter $$
create trigger sc_insert after insert on sc
for each row
begin
set @xh=0;
select sno from student where sno=new.sno into @xh;
if isnull(@xh) then
delete from sc where sno=new.sno;
end if;
end
$$
delimiter ;

触发:
insert into sc values(2011008,5,98);
insert into sc values(2011100,4,88);
select * from sc;
select * from student;

4、建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。如下表所示：

bank表：

transInfo表：

创建触发器，要求当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。

create database bankdb;
create table bank(customername char(10),cardid int,currentmoney float(10,2));
insert into bank values('张三',10011001,20000);
insert into bank values('李四',10011002,20000);
select * from bank;
create table transinfo(id int primary key auto_increment,transdate datetime default now(),cardid int,transtype enum('支取','存入'),transmoney int);


drop trigger if exists trig_insert_transinfo;
delimiter $$
create trigger trig_insert_transinfo after insert on transinfo
for each row
begin
set @transtype='';
set @transmoney=0;
set @cardid=0;
select cardid from transinfo where cardid=new.cardid into @cardid;
select transtype from transinfo where cardid=new.cardid into @transtype;
select transmoney from transinfo where cardid=new.cardid into @transmoney;
if @transtype='支取' then
update bank set currentmoney=currentmoney-@transmoney where cardid=@cardid;
else
update bank set currentmoney=currentmoney+@transmoney where cardid=@cardid;
end if;
end
$$
delimiter ;

insert into transinfo (cardid,transtype,transmoney) values(10011001,1,300);
insert into transinfo (cardid,transtype,transmoney) values(10011002,2,500);
select * from transinfo;
select * from bank;

5、使用root用户创建testuser1用户，初始密码设置为123456。让该用户对所有数据库拥有增删改查权限。

grant insert,update,delete,select on *.* to 'testuser1'@'localhost'
IDENTIFIED by '123456' with grant option;
flush privileges;

6、增加一个testuser2用户，密码为123456，只能在192.168.2.12上登录，并对数据库testdb有查询，增加，修改和删除的功能。

grant insert,update,delete,select on testdb.* 
to 'testuser2'@'192.168.2.12' IDENTIFIED by '123456'
with grant option;

7、用testuser1用户登录，将其密码修改为000000。

update mysql.user set password=password('000000') 
where user='testuser1' and host='localhost';

8、收回testuser2的所有权限。

revoke all on testdb.* from 'testuser2'@'192.168.2.12';

9、删除testuser2 用户。

drop user 'testuser2'@'192.168.2.12';