触发器及建表sql语句

下面是机构管理部分数据库的建立 create table users

(

username varchar(8) primary key,

passwd varchar(15) not null,

permit integer not null,

org varchar(30) not null

);

create table county

(

county_name varchar(10) primary key,

init char(2)

);

create table hospital

(

code char(4) primary key,

hos_name varchar(20) not NULL,

county varchar(10) not null,

foreign key(county) references county(county_name)

);

create table community

(

code char(4) primary key,

hos_name varchar(20) not NULL,

county varchar(10) not null,

foreign key(county) references county(county_name)

);

下面是机构管理的触发器和数据初始化

社区信息表community触发器所完成的功能:

1、插入功能:当向community输入一个新用户的时候会在users表默认生成两个账户,账户名为两位的区县代号+四位的社区代号+00(或者02),相应的账号密码为账号+888,，权限分别为33和65

2、更新功能:不能更新该社区所在的区县号,其它参数可以改变。修改社区相关信息时会改变users表下的相应账户信息,按照默认的情况同时更新信息上报员和账户管理员的用户名和密码

3、删除功能:如果删除社区，会同时在users表中删除该社区所拥有的信息上抱员和账户管理员的账户和密码

create or replace trigger community_trigger after insert or update or delete

on community for each row

begin

if inserting then

Insert into users(username,passwd,permit,org) values((select DISTINCT init from county where county_name=:NEW.county) || :NEW.code ||'00' ,(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '00' || '888' , 33 , :NEW.hos_name);

Insert into users(username,passwd,permit,org) values((select DISTINCT init from county where county_name=:NEW.county)||:NEW.code||'02',(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '02' || '888' ,65, :NEW.hos_name);

elsif updating then

Update users set

username=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '00',

passwd=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '00' || '888',

org=:NEW.hos_name

Where username=(select DISTINCT init from county where county_name=:OLD.county)||:OLD.code||'00';

Update users set

username=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '02',

passwd=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '02' || '888',

org=:NEW.hos_name

Where username=(select DISTINCT init from county where county_name=:OLD.county) || :OLD.code || '02';

elsif deleting then

Delete from users where username=(select DISTINCT init from county where county_name=:OLD.county) || :OLD.code ||'00';

Delete from users where username=(select DISTINCT init from county where county_name=:OLD.county) || :OLD.code ||'02';

end if;

end;

医院信息表hospital触发器完成三个功能：

(医院信息表hospital的触发器完成的功能和社区信息表触发器完成的功能差不多一样，有区别只是生成账号的权限不一样而已，其它都一样)

create or replace trigger hospital_trigger after insert or update or delete

on hospital for each row

begin

if inserting then

Insert into users(username,passwd,permit,org) values((select DISTINCT init from county where county_name=:NEW.county) || :NEW.code ||'00' ,(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '00' || '888' , 34 , :NEW.hos_name);

Insert into users(username,passwd,permit,org) values((select DISTINCT init from county where county_name=:NEW.county)||:NEW.code||'02',(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '02' || '888' ,66, :NEW.hos_name);

elsif updating then

Update users set

username=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '00',

passwd=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '00' || '888',

org=:NEW.hos_name

Where username=(select DISTINCT init from county where county_name=:OLD.county)||:OLD.code||'00';

Update users set

username=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '02',

passwd=(select DISTINCT init from county where county_name=:NEW.county) || :NEW.code || '02' || '888',

org=:NEW.hos_name

Where username=(select DISTINCT init from county where county_name=:OLD.county) || :OLD.code || '02';

elsif deleting then

Delete from users where username=(select DISTINCT init from county where county_name=:OLD.county) || :OLD.code ||'00';

Delete from users where username=(select DISTINCT init from county where county_name=:OLD.county) || :OLD.code ||'02';

end if;

end;

账户和密码注意:系统管理员可以修改账户和密码，其它用户只能修改自己的密码。

预定义先往数据库里添加信息：

添加区县信息：

insert into county(county_name,init) values('金山区','js');

insert into county(county_name,init) values('黄浦区','hp');

insert into county(county_name,init) values('徐汇区','xh');

insert into county(county_name,init) values('长宁区','cn');

insert into county(county_name,init) values('静安区','ja');

insert into county(county_name,init) values('普陀区','pt');

insert into county(county_name,init) values('闸北区','zb');

insert into county(county_name,init) values('虹口区','hk');

insert into county(county_name,init) values('杨浦区','yp');

insert into county(county_name,init) values('闵行区','mx');

insert into county(county_name,i …… 此处隐藏：4877字，全部文档内容请下载后查看。喜欢就下载吧 ……