-- 印章
CREATE TABLE base:user_seal_role (
	account_name VARCHAR(100),
	seal_number LVARCHAR(600)
)
 in datadbs1 ;
comment on table base:user_seal_role is '用户印章权限' ;
CREATE INDEX user_seal_role_account_name_idx ON base:user_seal_role (account_name) ;
comment on column base:user_seal_role.account_name is '用户账号';
comment on column base:user_seal_role.seal_number is 'user_seal_info.seal_number';

----------------------------------------
CREATE TABLE base:user_seal_info (
	seal_number VARCHAR(14) NOT NULL,
	org_name VARCHAR(100),
	seal_name VARCHAR(100),
	seal_md5 VARCHAR(32)
)
 in datadbs1 ;
comment on table base:user_seal_info is '粮食局印章' ;
CREATE INDEX user_seal_seal_number_idx ON base:user_seal_info (seal_number) ;
comment on column base:user_seal_info.seal_number is '印章编号';
comment on column base:user_seal_info.org_name is '单位名称';
comment on column base:user_seal_info.seal_name is '印章名称';
comment on column base:user_seal_info.seal_md5 is 'seal_number||`salt` md5';

ALTER TABLE base:user_seal_role ADD CONSTRAINT PRIMARY KEY (account_name) CONSTRAINT user_seal_role_pk ;
ALTER TABLE base:user_seal_role MODIFY account_name VARCHAR(100) NOT NULL ;
comment on column base:user_seal_role.account_name is '' ;
ALTER TABLE base:user_seal_role MODIFY seal_number VARCHAR(200) NOT NULL ;
comment on column base:user_seal_role.seal_number is '' ;

-------------------------------------
INSERT INTO base.user_seal_info (seal_number, org_name, seal_name, seal_md5)
VALUES ('1201030267193', '天津市津南区粮食购销有限公司', '天津市津南区粮食购销有限公司', md5('1201030267193salt'));

------------------------------------------
create procedure proc_tri_u_seal()
    referencing new as new for user_seal_info;
if new.seal_number is not null then
    let new.seal_md5 = md5(new.seal_number||'salt');
end if;
end procedure;

create trigger tri_u_seal_insert insert on user_seal_info
for each row (
 execute procedure proc_tri_u_seal() with trigger references
);
------------------------------------------

