yeartr.sql

这是用PB6开发的一个POS管理系统

use sypos2000
go

/*===========================================*/
/*					     */
/*     Create Trigger for Year Database	     */
/*					     */
/*	Last Modify Date  2000.07.13 	     */
/*  					     */
/*===========================================*/


if exists (select * from sysobjects where name='tr_checklog_Insert' and type='tr' )
	Drop Trigger tr_checklog_insert
go

CREATE TRIGGER tr_checklog_insert ON CHECKLOG 
FOR INSERT
AS

declare @deptno TDEPT
declare @groupno TGroup
declare @checkway char(1)
declare @LastDate datetime

if @@rowcount > 1 
   begin
    rollback
    Return
   end

select @deptno=deptno,@groupno=groupno,@checkway=checkway from inserted
delete from Checks from Checks a,goods b where a.goodsno=b.goodsno and 
           a.deptno=@deptno and b.groupno=@groupno
if @checkway='0' 
   begin
     insert into Checks ( deptno,goodsno,retailprice,acctqty,acctamt,costprice) 
	select @deptno,goodsno,retailprice,CurQty,CurCostAmt,CurCostPrice from VStock
     where deptno=@deptno and groupno=@groupno
    Return
   end
if @checkway='1'
  begin
     insert into Checks ( deptno,goodsno,retailprice,acctqty,acctamt,costprice) 
	select @deptno,goodsno,retailprice,CurQty,CurCostAmt,CurCostPrice from VStock
     where deptno=@deptno and groupno=@groupno and isnull(CurQty,0)<>0
    Return
  end
if @checkway='2'
 begin
   select @LastDate=(select max(startdate) from checklog where deptno=@deptno and groupno=@groupno)
     insert into Checks ( deptno,goodsno,retailprice,acctqty,acctamt,CostPrice) 
	select @deptno,goodsno,retailprice,CurQty,CurCostAmt,CurCostPrice from VStock
     where deptno=@deptno and groupno=@groupno and (isnull(CurQty,0)<>0 or ModifyDate >= @LastDate)

 end
GO
print 'Tr_CheckLog_Insert is created'
go

if exists ( select * from sysobjects where name='tr_checklog_update' and type='tr')
	drop trigger tr_checklog_update
go

CREATE TRIGGER tr_checklog_update ON CHECKLOG 
FOR UPDATE
AS

declare @deptno TDept
declare @groupno TGroup
Declare @EndDate TDate
Declare @CostMethod char(1)

if @@rowcount>1 
   begin
	   rollback
	   Return
   end 

if update(enddate)
   begin
   	select @deptno=deptno,@GroupNo=GroupNo,@enddate=enddate from inserted
		/* Update RealQty to Current Stock */
        update vstock set vstock.CheckQty=isnull(Checks.RealQty,0) - isnull(Checks.AcctQty,0),
			 VStock.CurQty=isnull(VStock.CurQty,0) + isnull(Checks.RealQty,0) - isnull(Checks.AcctQty,0),
			 VStock.CheckRetailAmt=isnull(VStock.CheckRetailAmt,0) + Checks.RetailPrice * (isnull(Checks.RealQty,0) - isnull(Checks.AcctQty,0)),
			 VStock.CheckAmt= isnull(VStock.CheckAmt,0) + Round(Checks.CostPrice * isnull(Checks.RealQty,0),2) - isnull(Checks.AcctAmt,0),
			 VStock.CurCostAmt= isnull(VStock.CurCostAmt,0) + Isnull(VStock.CurCostAmt,0) + Round(Checks.CostPrice * isnull(Checks.RealQty,0),2) - isnull(Checks.AcctAmt,0)
	from VStock,Checks
        where VStock.Deptno=Checks.deptno and VStock.GoodsNo=Checks.GoodsNo and 
		VStock.deptno=@deptno and Vstock.Groupno=@GroupNo 

		/* Backup Checks Data */
	Insert ChecksBackup (enddate,GoodsNo,DeptNo,AcctQty,RealQty,AcctAmt,RealAmt,RetailPrice,Operator,CostPrice)
        Select @EndDate,a.GoodsNo,a.DeptNo,a.AcctQty,a.RealQty,a.AcctAmt,Round(a.CostPrice * a.RealQty,2),a.RetailPrice,a.Operator,a.CostPrice
	From Checks a,Goods b
	Where a.Goodsno=b.GoodsNo and a.DeptNo=@DeptNo and b.GroupNo=@GroupNo
   end
go

Print 'tr_checklog_update is Created'

go



/*-----------------------------------------------------------------*/
/* 		Trigger for Update Auditflag 			*/
/*-----------------------------------------------------------------*/
	
if exists ( select * from sysobjects where name = 'tr_Loss_Update')
	drop trigger tr_Loss_Update
go


if exists ( select * from sysobjects where name = 'tr_Alloc_Update')
	drop trigger tr_Alloc_Update
go

if exists ( select * from sysobjects where name = 'tr_Adjust_Update')
	drop trigger tr_Adjust_Update
go

/*------------------------------------------------------------------------*/
/*              Trigger for Normal Flow  Insert 				*/
/*-----------------------------------------------------------------------*/

/****** Object:  Trigger dbo.tr_sale_insert   Script Date: 00-4-3 9:30:41 ******/

if exists ( select * from sysobjects where name = 'tr_sale_insert')
	drop trigger tr_sale_insert
go

CREATE TRIGGER TR_Sale_Insert ON dbo.Sale
FOR INSERT,Update
AS

declare @deptno char(4)
declare @Saleno char(10)
declare @AuditFlag char(1)
Declare @Deleted int

select @Saleno=Saleno,@deptno=DeptNo,@AuditFlag=AuditFlag from inserted
if @@RowCount <> 1 	Return
Select @Deleted=count(*) from deleted
if @deleted = 0 
 	Execute up_UpdateReceiptNo 'sale',@deptno,@SaleNo
else
  if Update(Auditflag) and @AuditFlag='1'
    Update Sale Set AuditDate=GetDate(),ReceiveDate=GetDate() where SaleNo=@saleNo

GO
print 'tr_sale is Created'
go

/****** Object:  Trigger dbo.tr_Import_insert   Script Date: 00-4-3 9:30:41 ******/
if exists ( select * from sysobjects where name = 'tr_import_insert')
	drop trigger tr_import_insert
go

CREATE TRIGGER TR_Import_Insert ON dbo.Import
FOR INSERT,Update
AS

declare @deptno char(4)
declare @Importno char(10)
declare @AuditFlag char(1)
Declare @Deleted int

select @Importno=Importno,@deptno=InputDept,@AuditFlag=AuditFlag from inserted
if @@RowCount <> 1 Return

Select @Deleted=count(*) from deleted
if @Deleted=0 
	Execute up_UpdateReceiptNo 'import',@DeptNo,@ImportNo
else
    if @AuditFlag='1' and update(auditflag)
       Update Import Set AuditDate=GetDate(),ReceiveDate=GetDate() where Importno=@Importno  /* modifyied 2000-08-04 */

go
print 'tr_import is created'
go

/****** Object:  Trigger dbo.tr_alloc_insert    Script Date: 00-4-3 14:47:31 ******/
if exists ( select * from sysobjects where name = 'tr_alloc_insert')
	drop trigger tr_alloc_insert
go

CREATE TRIGGER TR_Alloc_Insert ON dbo.Alloc
FOR INSERT,Update
AS

declare @deptno char(4)
declare @Allocno char(10)
Declare @Deleted int
Declare @AuditFlag char(1)

select @Allocno=Allocno,@deptno=DestDept,@AuditFlag=AuditFlag from inserted
if @@RowCount<>1 Return
Select @Deleted=count(*) from Deleted
if @Deleted=0
   Execute up_UpdateReceiptNo 'alloc',@DeptNo,@AllocNO
else
  if Update(Auditflag) and @AuditFlag='1'
    Update Alloc Set AuditDate=GetDate(),ReceiveDate=GetDate() where allocno=@allocno
go
print 'tr_alloc is created'
go

/****** Object:  Trigger dbo.tr_loss_insert   Script Date: 00-4-3 9:30:41 ******/
if exists ( select * from sysobjects where name = 'tr_loss_insert')
	Drop Trigger tr_loss_Insert
go

CREATE TRIGGER TR_loss_Insert ON dbo.loss
FOR INSERT,Update
AS

declare @deptno char(4)
declare @Lossno char(10)
Declare @AuditFlag char(1)
Declare @Deleted int

select @Lossno=Lossno,@deptno=DeptNo,@AuditFlag=AuditFlag from inserted
if @@RowCount<>1 Return
Select @Deleted=count(*) from Deleted
if @Deleted=0
	Execute up_UpdateReceiptNo 'Loss',@DeptNo,@LossNO
else
  if Update(Auditflag) and @AuditFlag='1'
    Update Loss Set AuditDate=GetDate(),ReceiveDate=GetDate() Where lossno=@LossNo
 

GO

print ' tr_loss is created'
go

/****** Object:  Trigger dbo.tr_adjust_insert   Script Date: 00-4-3 9:30:41 ******/
if exists ( select * from sysobjects where name = 'tr_adjust_insert')
	Drop Trigger tr_Adjust_Insert
go

CREATE TRIGGER TR_adjust_Insert ON dbo.adjust
FOR INSERT,update
AS

declare @deptno char(4)
declare @Adjustno char(10)
Declare @Deleted int
Declare @AuditFlag char(1)

select @Adjustno=Adjustno,@deptno=InputDept from inserted
if @@RowCount<>1 Return
Select @Deleted=count(*) from Deleted
if @Deleted=0
	Execute up_UpdateReceiptNo 'Adjust',@DeptNo,@AdjustNO
else
  if update(AuditFlag) and @AuditFlag='1'
     Update Adjust Set AuditDate=GetDate(),ReceiveDate=GetDate() where adjustno=@AdjustNo
GO
print 'tr_adjust is created'
go

/*-----------------------------------------------------------------------------------------*/
/*              Trigger for Normal Flow  Detail Insert 				*/
/*-----------------------------------------------------------------------------------------*/

/****** Object:  Trigger dbo.tr_allocdetail_insert    Script Date: 00-4-3 14:47:31 ******/
if exists ( select * from sysobjects where name = 'tr_allocdetail_insert')
	drop trigger tr_allocdetail_insert
go

CREATE trigger tr_allocdetail_insert
on allocdetail
for insert,update
as
declare @allocno char(10)
declare @goodsno char(13)
declare @qty Tqty
declare @costprice Tprice
declare @price Tprice
declare @ntaxprice tprice
declare @retailprice numeric(12,2)
declare @ntaxamt Tprice
declare @amt Tamt
declare @validdate datetime
declare @vendorno Tvendor
declare @receiptflag char(1)
declare @receiptno varchar(10)
declare @ReqAuditqty tqty
declare @ReachQty tqty
declare @srcdept tdept
declare @destdept tdept
declare @AuditQty TQty
declare @auditflag char(1)
declare @localflag char(1)
declare @reqno Treceipt

select @allocno = allocno,
	@reqno=reqno,
	@goodsno = goodsno,
	@qty =qty,
	@costprice = costprice,
	@price = price,
	@ntaxprice = ntaxprice,
	@retailprice = retailprice,
	@ntaxamt = ntaxamt,
	@amt = amt,
        @AuditQty = AuditQty,
	@validdate = validdate,
	@vendorno = vendorno
from inserted

select @receiptflag =receiptflag,
	@receiptno = receiptno,
	@srcdept = srcdept,
	@destdept = destdept,
	@auditflag = AuditFlag
 from alloc where allocno = @allocno

if @AuditFlag='0' 	Return

select @localflag=localflag from dept where deptno=@DestDept

if @LocalFlag='1' 
 update stock	set 
		 LastCostPrice = @Price,
		 allocinqty = isnull(allocinqty,0) + @Auditqty,
		 allocinamt = isnull(allocinamt,0) + @amt,
		 allocinretailamt = isnull(allocinretailamt,0) + round(@AuditQty*@retailprice,2),
		 curqty = isnull(curqty,0) + @AuditQty,