sql.txt

来自「一个简单的人事管理系统。。 基于VC++和SQL2005做的 老师要求的作业」· 文本 代码 · 共 107 行

CREATE DATABASE staff
GO


USE staff
GO
CREATE TABLE staff(
	sno int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	sname varchar(50) NOT NULL,
	sex varchar(50),
	date varchar(50),
	bumen varchar(50),
	wenhua varchar(50),
	zhicheng varchar(50),
	jobdate varchar(50),
	address varchar(50),
	state varchar(50),
	beizhu varchar(50)
)


USE staff
GO
CREATE TABLE users(
	username varchar(50) NOT NULL,
	pwd varchar(50) NOT NULL,
	type tinyint NOT NULL
)

use staff
insert into users values('admin','123',1)
go

use staff
go
CREATE TABLE lsjl(
	changeType varchar(20),
	changeTime datetime,
	sno varchar(50) NOT NULL,
	sname varchar(50),
	sex varchar(50),
	date varchar(50),
	bumen varchar(50),
	wenhua varchar(50),
	zhicheng varchar(50),
	jobdate varchar(50),
	address varchar(50),
	state varchar(50),
	beizhu varchar(50)
)


use staff
go
create trigger DateChange on staff for delete,insert,update 
as 
declare @insertedCount int 
declare @deletedCount int 
declare @changeType char(10) 
declare @changeTime datetime 
declare @updateType char(10) 
/*在ms sql server中有两个临时表保存着被删除和被插入的记录，分别叫“deleted”，“inserted”。update可以看作一次删除和一次添加*/ 
select @insertedCount=count(*) from inserted 
select @deletedCount=count(*) from deleted 
select @changeType= 
case 
when @insertedCount>0 and @deletedCount=0 
then 'insert' 
when @insertedCount=0 and @deletedCount>0 
then 'delete' 
else 'update' 
end
select @changeTime=GetDate() 
select @updateType='' 
if @changeType='update' select @updateType='before' 

insert into lsjl(changeType,changeTime,sno,sname,sex,date,bumen,wenhua,zhicheng,jobdate,address,state,beizhu) select @changeType+@updateType,@changeTime,sno,sname,sex,date,bumen,wenhua,zhicheng,jobdate,address,state,beizhu from deleted 

if @changeType='update' select @updateType='later' 

insert into lsjl(changeType,changeTime,sno,sname,sex,date,bumen,wenhua,zhicheng,jobdate,address,state,beizhu) select @changeType+@updateType,@changeTime,sno,sname,sex,date,bumen,wenhua,zhicheng,jobdate,address,state,beizhu from inserted









/*
以下为我自己整理的一点数据，可运行以调试程序
----------------------------------------------------*/

use staff
insert into staff values('王强','男','1988-02-11','生产部','大学本科','职员','2009-02-15','湖北宜昌','在职','')
insert into staff values('李兵','男','1967-01-11','技术部','大学专科','总管','1994-12-05','广西柳州','退休','达到年龄,正常退休')
insert into staff values('叶之奎','男','1992-05-26','生产部','中专','职员','2007-04-08','上海','在职','')
insert into staff values('邓莉莉','女','1964-09-15','财务部','小学','总管','1998-05-12','北京','在职','公事出差')
insert into staff values('朱莉','女','1982-06-11','生产部','高中','职员','2005-05-16','广东佛山','离职','开除')
insert into staff values('陈君','男','1975-11-12','技术部','研究生','总管','2003-02-16','台湾','在职','')
insert into staff values('贺萌萌','女','1989-11-12','宣传部','大学本科','实行','2009-2-16','湖北武汉','在职','')