触发器及建表sl语句.doc

下面是机构管理部分数据库的建立 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触发器所完成的功能: 插入功能:当向community输入一个新用户的时候会在users表默认生成两个账户,账户名为两位的区县代号+四位的社区代号+00(或者02),相应的账号密码为账号+888,，权限分别为33和65 更新功能:不能更新该社区所在的区县号,其它参数可以改变。修改社区相关信息时会改变users表下的相应账户信息,按照默认的情况同时更新信息上报员和账户管理员的用户名和密码 删除功能:如果删除社区，会同时在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