SQL-SYBASE存储过程

存储过程自动记录操作记录(DDL-SQL)
Auto save user operate_log(select/insert/update/delete)

with sybase database procedure

 

Sybase 数据库在中国的应用领域 ( 税务 , 保险 , 银行 , 财政 )

author:chinayaosir,QQ:44633197
blog:http://blog.csdn.net/chinayaosir

 

--------------------------------------------------------------------------------------------
//1.create a table for testting
create table dbo.b_bom_user (
usercode char(20) not null,
username char(20) not null,
password char(10) null,
user_dept char(20) null,
officename char(1) null
) ;
alter table dbo.b_bom_user
add constraint b_bom_user_Px primary key (usercode) ;

//2.user table operate log with call procedure
drop table dbo.b_bom_user_log;
create table dbo.b_bom_user_log(
operate_time    datetime not null,
operate_name    varchar(20) not null,
operate_detail    text null
);
alter table b_bom_user_log
add constraint b_bom_user_log_Pk primary key (operate_time,operate_name) ;

//3.b_bom_user table procedure list

//3.1 b_bom_user_select procedure
drop procedure dbo.b_bom_user_select;
create procedure dbo.b_bom_user_select
as
begin
    select
    dbo.b_bom_user.usercode,
    dbo.b_bom_user.username,
    dbo.b_bom_user.password,
    dbo.b_bom_user.user_dept,
    dbo.b_bom_user.officename
    from  b_bom_user

    insert into b_bom_user_log
    values(getdate(),'b_bom_user_select','select usercode,username,password,user_dept,officename from b_bom_user')
   
end
;

//3.2 b_bom_user_insert procedure
drop procedure dbo.b_bom_user_insert;
create procedure dbo.b_bom_user_insert
@usercode char(20),
@username char(20),
@password char(10),
@user_dept char(20),
@officename char(1)
as
begin   
    insert into b_bom_user(usercode,username,password,user_dept,officename)
    values(@usercode,@username,@password,@user_dept,@officename)
    insert into b_bom_user_log
    values(getdate(),'b_bom_user_insert','insert into b_bom_user'+@usercode+@username+@password+@user_dept+@officename)
end
;

//3.3 b_bom_user_delete procedure
drop procedure dbo.b_bom_user_delete;
create procedure dbo.b_bom_user_delete
@usercode char(20)
as
begin
    delete from  b_bom_user where usercode = @usercode
    insert into b_bom_user_log
    values(getdate(),'b_bom_user_delete','delete from  b_bom_user where usercode ='+@usercode)

end
;
//3.4 b_bom_user_update procedure
drop  procedure dbo.b_bom_user_update;
create procedure dbo.b_bom_user_update
@usercode char(20),
@username_old char(20),
@username_new char(20),
@password_old char(10),
@password_new char(10),
@user_dept_old char(20),
@user_dept_new char(20),
@officename_old char(1),
@officename_new char(1)
as
begin
    update b_bom_user set
        username = @username_new,
        password = @password_new ,
        user_dept = @user_dept_new,
        officename = @officename_new
    where
        usercode = @usercode and
        username = @username_old and
        password = @password_old and
        user_dept = @user_dept_old and
        officename = @officename_old
    insert into b_bom_user_log
    values(getdate(),'b_bom_user_update','update b_bom_user update username = '+@username_new+' password ='+@password_new+' user_dept='+@user_dept_new+' officename ='+@officename_new+' where usercode ='+@usercode)
end
;

//4.c/s program sample,pls used baidu/google
call procedure with pb datawindow


//5.b/s program sample,pls used baidu/google
call procedure with jsp/javabean/ejb3.0