銷售管理系統(tǒng)相關(guān)代碼

1、-銷售管理系統(tǒng)相關(guān)代碼 班級(jí):計(jì)科學(xué)061 學(xué)號(hào):061004103 姓名:方爽-創(chuàng)建數(shù)據(jù)庫(kù)IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N銷售管理系統(tǒng))DROP DATABASE 銷售管理系統(tǒng)GOCREATE DATABASE 銷售管理系統(tǒng) ON (NAME = N銷售管理系統(tǒng)_Data, FILENAME = NF:銷售管理系統(tǒng)_SQL銷售管理系統(tǒng)_Data.MDF ,SIZE = 2, FILEGROWTH = 10%) LOG ON (NAME = N銷售管理系統(tǒng)_Log, FILENAME = NF:

2、銷售管理系統(tǒng)_SQL銷售管理系統(tǒng)_Log.LDF , SIZE = 1, FILEGROWTH = 10%) COLLATE Chinese_PRC_CI_ASGOexec sp_dboption N銷售管理系統(tǒng), Nautoclose, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Nbulkcopy, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Ntrunc. log, NtrueGOexec sp_dboption N銷售管理系統(tǒng), Ntorn page detection, NfalseGOexec sp_dboption N銷售管理系統(tǒng),

3、 Nread only, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Ndbo use, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Nsingle, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Nautoshrink, NtrueGOexec sp_dboption N銷售管理系統(tǒng), NANSI null default, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Nrecursive triggers, NfalseGOexec sp_dboption N銷售管理系統(tǒng), NANSI nulls

4、, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Nconcat null yields null, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Ncursor close on commit, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Ndefault to local cursor, NfalseGOexec sp_dboption N銷售管理系統(tǒng), Nquoted identifier, NfalseGOexec sp_dboption N銷售管理系統(tǒng), NANSI warnings, NfalseGOexec sp

5、_dboption N銷售管理系統(tǒng), Nauto create statistics, NtrueGOexec sp_dboption N銷售管理系統(tǒng), Nauto update statistics, NtrueGOif( (xxmicrosoftversion / power(2, 24) = 8) and (xxmicrosoftversion & 0 xffff = 724) )exec sp_dboption N銷售管理系統(tǒng), Ndb chaining, NfalseGOuse 銷售管理系統(tǒng)GO-檢查當(dāng)前數(shù)據(jù)庫(kù)是否已存在相應(yīng)表if exists (select * from dbo.

6、sysobjects where id = object_id(Ndbo.FK_客戶訂貨表_商品基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客戶訂貨表 DROP CONSTRAINT FK_客戶訂貨表_商品基礎(chǔ)信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_庫(kù)存表_商品基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.庫(kù)存表 DRO

7、P CONSTRAINT FK_庫(kù)存表_商品基礎(chǔ)信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_銷售表_商品基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.銷售表 DROP CONSTRAINT FK_銷售表_商品基礎(chǔ)信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_銷售退貨表_商品基礎(chǔ)信息表) and OBJECTP

8、ROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.銷售退貨表 DROP CONSTRAINT FK_銷售退貨表_商品基礎(chǔ)信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客戶訂貨表_客戶基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客戶訂貨表 DROP CONSTRAINT FK_客戶訂貨表_客戶基礎(chǔ)信息表GOif exists (select * from dbo.sy

9、sobjects where id = object_id(Ndbo.FK_客戶結(jié)款表_客戶基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客戶結(jié)款表 DROP CONSTRAINT FK_客戶結(jié)款表_客戶基礎(chǔ)信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客戶訂貨表_訂貨票號(hào)表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客戶訂貨表 DRO

10、P CONSTRAINT FK_客戶訂貨表_訂貨票號(hào)表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_銷售表_銷售票號(hào)表2) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.銷售表 DROP CONSTRAINT FK_銷售表_銷售票號(hào)表2GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_銷售退貨表_銷售退單票號(hào)表) and OBJECTPRO

11、PERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.銷售退貨表 DROP CONSTRAINT FK_銷售退貨表_銷售退單票號(hào)表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.FK_客戶結(jié)款記錄表_客戶結(jié)款表) and OBJECTPROPERTY(id, NIsForeignKey) = 1)ALTER TABLE dbo.客戶結(jié)款記錄表 DROP CONSTRAINT FK_客戶結(jié)款記錄表_客戶結(jié)款表GOif exists (select * from dbo.sy

12、sobjects where id = object_id(Ndbo.銷售管理視圖) and OBJECTPROPERTY(id, NIsView) = 1)drop view dbo.銷售管理視圖GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客戶結(jié)款記錄表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客戶結(jié)款記錄表GOif exists (select * from dbo.sysobjects where id = object_i

13、d(Ndbo.客戶結(jié)款表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客戶結(jié)款表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客戶訂貨表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客戶訂貨表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.庫(kù)存表) and OBJECTPROPER

14、TY(id, NIsUserTable) = 1)drop table dbo.庫(kù)存表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.銷售表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.銷售表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.銷售退貨表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop tabl

15、e dbo.銷售退貨表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.商品基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.商品基礎(chǔ)信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.客戶基礎(chǔ)信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.客戶基礎(chǔ)信息表GOif exis

16、ts (select * from dbo.sysobjects where id = object_id(Ndbo.操作員信息表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.操作員信息表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.訂貨票號(hào)表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.訂貨票號(hào)表GOif exists (select * from dbo.syso

17、bjects where id = object_id(Ndbo.銷售票號(hào)表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.銷售票號(hào)表GOif exists (select * from dbo.sysobjects where id = object_id(Ndbo.銷售退單票號(hào)表) and OBJECTPROPERTY(id, NIsUserTable) = 1)drop table dbo.銷售退單票號(hào)表GO-創(chuàng)建用戶if not exists (select * from master.dbo.syslogins wh

18、ere loginname = N山)BEGINdeclare xlogindb nvarchar(一三2), xloginlang nvarchar(一三2) select xlogindb = N銷售管理系統(tǒng), xloginlang = N簡(jiǎn)體中文if xlogindb is null or not exists (select * from master.dbo.sysdatabases where name = xlogindb)select xlogindb = Nmasterif xloginlang is null or (not exists (select * from ma

19、ster.dbo.syslanguages where name = xloginlang) and xloginlang Nus_english)select xloginlang = xxlanguageexec sp_addlogin N山, null, xlogindb, xloginlangENDGOif not exists (select * from dbo.sysusers where name = Nguest and hasdbaccess = 1)EXEC sp_grantdbaccess NguestGOif not exists (select * from dbo

20、.sysusers where name = N山)EXEC sp_grantdbaccess N山, N山GO-創(chuàng)建數(shù)據(jù)庫(kù)表CREATE TABLE dbo.商品基礎(chǔ)信息表 (商品編號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,簡(jiǎn)稱 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,規(guī)格 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,產(chǎn)地 varchar

21、(30) COLLATE Chinese_PRC_CI_AS NULL ,單位 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,保質(zhì)期 char (5) COLLATE Chinese_PRC_CI_AS NULL ,進(jìn)價(jià) money NULL ,批發(fā)價(jià) money NULL ,零售價(jià) money NULL ,備注 varchar (60) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.客戶基礎(chǔ)信息表 (客戶編號(hào) varchar (20) COLLATE Chinese_PR

22、C_CI_AS NOT NULL ,客戶全稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,簡(jiǎn)稱 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,地址 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,傳真 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,聯(lián)系人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,聯(lián)系人電話 varchar (30) COLLATE Chinese_PRC_

23、CI_AS NULL ,開(kāi)戶銀行 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,納稅人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,郵箱網(wǎng)址 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,郵政編碼 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,銀行賬號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.操作員信息表 (操

24、作員編號(hào) varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,操作員姓名 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作員密碼 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作員級(jí)別 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,時(shí)間 datetime NOT NULL ) ON PRIMARYGOCREATE TABLE dbo.訂貨票號(hào)表 (訂單號(hào) varchar (30) COLLATE Chinese_P

25、RC_CI_AS NOT NULL ,品種數(shù) int NULL ,數(shù)量 int NULL ,應(yīng)付金額 money NULL ,訂貨時(shí)間 datetime NULL ,經(jīng)手人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作員 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,是否可用 int NULL ) ON PRIMARYGOCREATE TABLE dbo.銷售票號(hào)表 (銷售票號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,品種數(shù) int NULL

26、,數(shù)量 int NULL ,應(yīng)付金額 money NULL ,付款方式 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,實(shí)付金額 money NULL ,銷售時(shí)間 datetime NULL ,經(jīng)手人 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ,操作員 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.銷售退單票號(hào)表 (銷售退單票號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS

27、 NOT NULL ,品種數(shù) int NULL ,數(shù)量 int NULL ,應(yīng)付金額 money NULL ,付款方式 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,實(shí)付金額 money NULL ,退貨時(shí)間 datetime NOT NULL ,經(jīng)手人 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,操作員 varchar (20) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.客戶結(jié)款表 (客戶編號(hào) varchar (20) C

28、OLLATE Chinese_PRC_CI_AS NOT NULL ,客戶全稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,金額小計(jì) money NULL ,欠款記賬 money NULL ,付款合計(jì) money NULL ,結(jié)款時(shí)間 datetime NULL ) ON PRIMARYGOCREATE TABLE dbo.客戶訂貨表 (客戶編號(hào) varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,客戶名稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,商品編

29、號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,數(shù)量 int NULL ,金額 money NULL ,折扣 float NULL ,訂貨票號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGOCREATE TABLE dbo.庫(kù)存表 (商品編號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名稱 varchar (30)

30、 COLLATE Chinese_PRC_CI_AS NULL ,庫(kù)存數(shù)量 int NULL ,庫(kù)存金額 money NULL ,盤(pán)點(diǎn) int NULL ) ON PRIMARYGOCREATE TABLE dbo.銷售表 (商品編號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客戶編號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客戶全稱 varchar (30) COLLATE Chinese

31、_PRC_CI_AS NULL ,數(shù)量 int NULL ,金額 money NULL ,折扣 float NULL ,銷售票號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON PRIMARYGOCREATE TABLE dbo.銷售退貨表 (商品編號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,商品名稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,客戶編號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS N

32、ULL ,客戶全稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,數(shù)量 int NULL ,金額 money NULL ,折扣 float NULL ,銷售退單票號(hào) varchar (30) COLLATE Chinese_PRC_CI_AS NOT NULL ) ON PRIMARYGOCREATE TABLE dbo.客戶結(jié)款記錄表 (客戶編號(hào) varchar (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,客戶全稱 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ,結(jié)款金

33、額 money NULL ,結(jié)款時(shí)間 datetime NULL ,操作員 varchar (30) COLLATE Chinese_PRC_CI_AS NULL ) ON PRIMARYGO-創(chuàng)建主鍵約束ALTER TABLE dbo.商品基礎(chǔ)信息表 WITH NOCHECK ADD CONSTRAINT PK_商品基礎(chǔ)信息表 PRIMARY KEY CLUSTERED (商品編號(hào)) ON PRIMARY GOALTER TABLE dbo.客戶基礎(chǔ)信息表 WITH NOCHECK ADD CONSTRAINT PK_客戶基礎(chǔ)信息表 PRIMARY KEY CLUSTERED (客戶編號(hào))

34、ON PRIMARY GOALTER TABLE dbo.操作員信息表 WITH NOCHECK ADD CONSTRAINT PK_操作員信息表 PRIMARY KEY CLUSTERED (操作員編號(hào)) ON PRIMARY GOALTER TABLE dbo.訂貨票號(hào)表 WITH NOCHECK ADD CONSTRAINT PK_訂貨票號(hào)表 PRIMARY KEY CLUSTERED (訂單號(hào)) ON PRIMARY GOALTER TABLE dbo.銷售票號(hào)表 WITH NOCHECK ADD CONSTRAINT PK_銷售票號(hào)表 PRIMARY KEY CLUSTERED (銷

35、售票號(hào)) ON PRIMARY GOALTER TABLE dbo.銷售退單票號(hào)表 WITH NOCHECK ADD CONSTRAINT PK_銷售退單票號(hào)表 PRIMARY KEY CLUSTERED (銷售退單票號(hào)) ON PRIMARY GOALTER TABLE dbo.客戶結(jié)款表 WITH NOCHECK ADD CONSTRAINT PK_客戶結(jié)款表 PRIMARY KEY CLUSTERED (客戶編號(hào)) ON PRIMARY GOALTER TABLE dbo.客戶訂貨表 WITH NOCHECK ADD CONSTRAINT PK_客戶訂貨表 PRIMARY KEY CLU

36、STERED (客戶編號(hào),商品編號(hào)) ON PRIMARY GOALTER TABLE dbo.庫(kù)存表 WITH NOCHECK ADD CONSTRAINT PK_庫(kù)存表 PRIMARY KEY CLUSTERED (商品編號(hào)) ON PRIMARY GOALTER TABLE dbo.銷售表 WITH NOCHECK ADD CONSTRAINT PK_銷售表 PRIMARY KEY CLUSTERED (銷售票號(hào)) ON PRIMARY GOALTER TABLE dbo.銷售退貨表 WITH NOCHECK ADD CONSTRAINT PK_銷售退貨表 PRIMARY KEY CLU

37、STERED (銷售退單票號(hào)) ON PRIMARY GOALTER TABLE dbo.客戶結(jié)款記錄表 WITH NOCHECK ADD CONSTRAINT PK_客戶結(jié)款記錄表 PRIMARY KEY CLUSTERED (客戶編號(hào)) ON PRIMARY GO-創(chuàng)建默認(rèn)值約束ALTER TABLE dbo.操作員信息表 ADD CONSTRAINT DF_操作員信息表_時(shí)間 DEFAULT (getdate() FOR 時(shí)間GOALTER TABLE dbo.銷售票號(hào)表 ADD CONSTRAINT DF_銷售票號(hào)表_銷售時(shí)間 DEFAULT (getdate() FOR 銷售時(shí)間GO

38、ALTER TABLE dbo.銷售退單票號(hào)表 ADD CONSTRAINT DF_銷售退單票號(hào)表_退貨時(shí)間 DEFAULT (getdate() FOR 退貨時(shí)間GO-創(chuàng)建默認(rèn)值約束ALTER TABLE dbo.客戶結(jié)款表 ADD CONSTRAINT DF_客戶結(jié)款表_結(jié)款時(shí)間 DEFAULT (getdate() FOR 結(jié)款時(shí)間,CONSTRAINT IX_客戶結(jié)款表 UNIQUE NONCLUSTERED (客戶編號(hào)) ON PRIMARY GO-創(chuàng)建默認(rèn)值約束ALTER TABLE dbo.庫(kù)存表 ADDCONSTRAINT DF_庫(kù)存表_盤(pán)點(diǎn) DEFAULT (0) FOR 盤(pán)

39、點(diǎn)GOALTER TABLE dbo.客戶結(jié)款記錄表 ADD CONSTRAINT DF_客戶結(jié)款記錄表_結(jié)款時(shí)間 DEFAULT (getdate() FOR 結(jié)款時(shí)間GOALTER TABLE dbo.客戶結(jié)款表 ADD CONSTRAINT FK_客戶結(jié)款表_客戶基礎(chǔ)信息表 FOREIGN KEY (客戶編號(hào)) REFERENCES dbo.客戶基礎(chǔ)信息表 (客戶編號(hào))GOALTER TABLE dbo.客戶訂貨表 ADD CONSTRAINT FK_客戶訂貨表_訂貨票號(hào)表 FOREIGN KEY (訂貨票號(hào)) REFERENCES dbo.訂貨票號(hào)表 (訂單號(hào)),CONSTRAINT

40、FK_客戶訂貨表_客戶基礎(chǔ)信息表 FOREIGN KEY (客戶編號(hào)) REFERENCES dbo.客戶基礎(chǔ)信息表 (客戶編號(hào)),CONSTRAINT FK_客戶訂貨表_商品基礎(chǔ)信息表 FOREIGN KEY (商品編號(hào)) REFERENCES dbo.商品基礎(chǔ)信息表 (商品編號(hào))GOALTER TABLE dbo.庫(kù)存表 ADD CONSTRAINT FK_庫(kù)存表_商品基礎(chǔ)信息表 FOREIGN KEY (商品編號(hào)) REFERENCES dbo.商品基礎(chǔ)信息表 (商品編號(hào))GOALTER TABLE dbo.銷售表 ADD CONSTRAINT FK_銷售表_商品基礎(chǔ)信息表 FOREIG

41、N KEY (商品編號(hào)) REFERENCES dbo.商品基礎(chǔ)信息表 (商品編號(hào)),CONSTRAINT FK_銷售表_銷售票號(hào)表2 FOREIGN KEY (銷售票號(hào)) REFERENCES dbo.銷售票號(hào)表 (銷售票號(hào))GOALTER TABLE dbo.銷售退貨表 ADD CONSTRAINT FK_銷售退貨表_商品基礎(chǔ)信息表 FOREIGN KEY (商品編號(hào)) REFERENCES dbo.商品基礎(chǔ)信息表 (商品編號(hào)),CONSTRAINT FK_銷售退貨表_銷售退單票號(hào)表 FOREIGN KEY (銷售退單票號(hào)) REFERENCES dbo.銷售退單票號(hào)表 (銷售退單票號(hào))G

42、OALTER TABLE dbo.客戶結(jié)款記錄表 ADD CONSTRAINT FK_客戶結(jié)款記錄表_客戶結(jié)款表 FOREIGN KEY (客戶編號(hào)) REFERENCES dbo.客戶結(jié)款表 (客戶編號(hào))GOSET QUOTED_IDENTIFIER ON GOSET ANSI_NULLS ON GO-創(chuàng)建視圖CREATE VIEW dbo.銷售管理視圖ASSELECT dbo.訂貨票號(hào)表.訂單號(hào), dbo.庫(kù)存表.商品編號(hào), dbo.銷售退貨表.銷售退單票號(hào), dbo.商品基礎(chǔ)信息表.商品編號(hào) AS Expr1, dbo.客戶訂貨表.客戶編號(hào), dbo.客戶訂貨表.商品編號(hào) AS Expr

43、2, dbo.客戶基礎(chǔ)信息表.客戶編號(hào) AS Expr3, dbo.銷售表.銷售票號(hào), dbo.銷售票號(hào)表.銷售票號(hào) AS Expr4, dbo.客戶結(jié)款表.客戶編號(hào) AS Expr5, dbo.客戶結(jié)款記錄表.客戶編號(hào) AS Expr6, dbo.銷售退單票號(hào)表.銷售退單票號(hào) AS Expr7FROM dbo.訂貨票號(hào)表 INNER JOIN dbo.客戶訂貨表 ON dbo.訂貨票號(hào)表.訂單號(hào) = dbo.客戶訂貨表.訂貨票號(hào) INNER JOIN dbo.客戶基礎(chǔ)信息表 ON dbo.客戶訂貨表.客戶編號(hào) = dbo.客戶基礎(chǔ)信息表.客戶編號(hào) INNER JOIN dbo.客戶結(jié)款表 O

44、N dbo.客戶基礎(chǔ)信息表.客戶編號(hào) = dbo.客戶結(jié)款表.客戶編號(hào) INNER JOIN dbo.客戶結(jié)款記錄表 ON dbo.客戶結(jié)款表.客戶編號(hào) = dbo.客戶結(jié)款記錄表.客戶編號(hào) INNER JOIN dbo.庫(kù)存表 ON dbo.客戶訂貨表.商品編號(hào) = dbo.庫(kù)存表.商品編號(hào) INNER JOIN dbo.商品基礎(chǔ)信息表 ON dbo.客戶訂貨表.商品編號(hào) = dbo.商品基礎(chǔ)信息表.商品編號(hào) AND dbo.庫(kù)存表.商品編號(hào) = dbo.商品基礎(chǔ)信息表.商品編號(hào) INNER JOIN dbo.銷售表 ON dbo.商品基礎(chǔ)信息表.商品編號(hào) = dbo.銷售表.商品編號(hào) IN

45、NER JOIN dbo.銷售票號(hào)表 ON dbo.銷售表.銷售票號(hào) = dbo.銷售票號(hào)表.銷售票號(hào) INNER JOIN dbo.銷售退貨表 ON dbo.商品基礎(chǔ)信息表.商品編號(hào) = dbo.銷售退貨表.商品編號(hào) INNER JOIN dbo.銷售退單票號(hào)表 ON dbo.銷售退貨表.銷售退單票號(hào) = dbo.銷售退單票號(hào)表.銷售退單票號(hào)GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO-創(chuàng)建觸發(fā)器create trigger tri11on 庫(kù)存表for insert,update,deleteas print 數(shù)據(jù)修改成功！goins

46、ert into 庫(kù)存表values(S20081228003,餅干,50,500,0)gocreate trigger tri2on 操作員信息表for insert,update,deleteas print 數(shù)據(jù)修改成功！gocreate trigger tri3on 訂貨票號(hào)表for insert,update,deleteas print 數(shù)據(jù)修改成功！gocreate trigger tri4on 客戶訂貨表for insert,update,deleteas print 數(shù)據(jù)修改成功！gocreate trigger tri5on 客戶基礎(chǔ)信息表for insert,update,deleteas print 數(shù)據(jù)修改成功！gocreate trigger tri6on 銷售表for insert,update,deleteas print 數(shù)據(jù)修改成功！go-創(chuàng)建觸發(fā)器use 銷售管理系統(tǒng)gocreate trigger 商品信息_tron 商品基礎(chǔ)信息