sypostr.sql

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

/******************************************/
/* Microsoft SQL Server - Scripting			*/
/* Server: BZP					*/
/* Database: sypos					*/
/* Creation Date 00-5-13 21:27:59 			*/
/******************************************/
use sypos 
go

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

Create Trigger tr_funcs on funcs
for insert,Delete
as
 Delete From OperatorRights where name in ( select name from deleted)
 Insert OperatorRights (name,pname,operator)
 Select name,pname,'root' 
 from inserted
go

/****** Object:  Trigger tr_class_update    Script Date: 00-4-3 9:30:40 ******/
if exists ( select * from sysobjects where name='tr_class_update' and type='tr')
	Drop Trigger tr_class_update
go

CREATE TRIGGER tr_class_update
ON class
FOR UPDATE
AS
  UPDATE Class SET ModifyDate=GetDate() 
  where ClassNo in (select ClassNo from inserted)

go

/****** Object:  Trigger tr_class_delete    Script Date: 00-4-3 9:30:40 ******/
if exists ( select * from sysobjects where name='tr_class_delete' and type='tr')
	Drop Trigger tr_class_delete
go

CREATE TRIGGER tr_class_delete
ON class
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'class',ClassNo,GetDate() from Deleted
GO

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

CREATE TRIGGER tr_Cust_update
ON Cust
FOR UPDATE
AS
  UPDATE Cust SET ModifyDate=GetDate() 
  where CustNo in (select CustNo from inserted)
GO

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

CREATE TRIGGER tr_Cust_delete
ON Cust
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'cust',CustNO,GetDate() from Deleted

GO
/****** Object:  Trigger tr_custprice_delete    Script Date: 00-4-3 9:30:40 ******/
if exists ( select * from sysobjects where name='tr_CustPrice_Delete' and type='tr')
	 Drop Trigger tr_custPrice_delete
go

CREATE TRIGGER tr_custprice_delete
ON custprice
FOR DELETE
AS
  insert DelData(TableName,key1,key2,DelDate) 
  select 'CustPrice',CustNo,GoodsNo,GetDate() from Deleted
GO

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

CREATE TRIGGER Tr_CustPrice_Update ON CUSTPRICE 
FOR UPDATE
AS
  Update  CustPrice Set ModifyDate=getdate()
  From CustPrice,Inserted
  Where CustPrice.CustNo=Inserted.CustNo and 
	CustPrice.GoodsNo=Inserted.GoodsNo
GO

if exists ( select * from sysobjects where name='tr_DeptTableRights_Delete' and type='tr')
	Drop Trigger tr_DeptTableRights_Delete
go
CREATE TRIGGER Tr_DeptTableRights_Delete ON DEPTTABLERIGHTS 
FOR Delete
AS
  Insert Deldata (tableName,key1,Key2,Key3)
  select DeptNo,TableName,FieldName,getDate()
  From Deleted

GO

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

CREATE TRIGGER Tr_DeptTableRights_Update ON DEPTTABLERIGHTS 
FOR UPDATE
AS
 Update DeptTableRights Set ModifyDate=Getdate()
 from DepttableRights,Inserted
 Where DeptTableRights.TableName=Inserted.TableName and
	DeptTablerights.DeptNo=Inserted.DeptNo and
	DeptTableRights.FieldName=Inserted.FieldName
GO

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

CREATE TRIGGER tr_goods_insert
ON goods
FOR INSERT
AS

   declare @DeptNo TDept
   declare c1 cursor for select DeptNo from Dept where LocalFlag='1'
   open c1
   fetch c1 into @DeptNo
   while @@FETCH_STATUS=0
   begin
	/* Insert into Table Stock */
     insert stock(GoodsNo,DeptNo,RetailPrice,LastCostPrice,CurCostPrice,InitCostPrice)
     select GoodsNo,@DeptNo,RetailPrice,EvaluatePrice,EvaluatePrice,EvaluatePrice
     from inserted
	/* Insert into table TopAndBottom */
     Insert TopAndBottom (GoodsNo,DeptNo) select GoodsNo,@DeptNo from Inserted
     Insert DeptPs (DeptNO,GoodsNo,RetailPrice,SalePrice,MemberPrice,SpriceFlag)
	 Select @DeptNO,GoodsNo,RetailPrice,SalePrice,MemberPrice,SPriceFlag From Inserted

     fetch c1 into @DeptNo
   end
   deallocate c1
   Insert GoodsGrade (GoodsNo) Select GoodsNo From Inserted
   Insert VendorPrice (GoodsNo,VendorNo,TaxPrice,NtaxPrice)
 	Select GoodsNo,VendorNo,EvaluatePrice,NEvaluatePrice
	From Inserted Where VendorNo is not null
GO

/****** Object:  Trigger tr_goods_delete    Script Date: 00-4-3 9:30:41 ******/
if exists ( select * from sysobjects where name='tr_goods_delete' and type='tr')
	Drop Trigger tr_goods_Delete
go

CREATE TRIGGER tr_goods_delete
ON goods
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'goods',GoodsNo,GetDate() from Deleted
   Delete from Stock Where GoodsNO in ( Select  goodsno from Deleted)
   Delete from deptps  Where GoodsNO in ( Select  goodsno from Deleted)
   Delete from goodsgrade Where GoodsNO in ( Select  goodsno from Deleted)
   Delete from topandBottom Where GoodsNO in ( Select  goodsno from Deleted)
   Delete from VendorPrice Where GoodsNO in ( Select  goodsno from Deleted)
GO
print 'tr_goods_deleted is Creaded!'
go

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

CREATE TRIGGER tr_goods_update
ON goods
FOR UPDATE
AS
 
   if Update(RetailPrice) 
	Update Stock set Stock.RetailPrice=Inserted.RetailPrice
          From Stock,Inserted
          Where Stock.GoodsNO = Inserted.GoodsNO

  update goods set ModifyDate=GetDate() 
  	where GoodsNo in (select GoodsNo from inserted)
GO

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

CREATE TRIGGER Tr_GoodsDis_Update ON GOODSDIS 
FOR UPDATE
AS
  Update GoodsDis Set Modifydate=Getdate()
  From Goodsdis,Inserted
  Where GoodsDis.GoodsNo=Inserted.GoodsNo
GO

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

CREATE TRIGGER Tr_Goodsdis_Delete ON GOODSDIS 
FOR DELETE 
AS
  Insert Deldata ( tableName,key1,DelDate)
  Select 'goodsdis',GoodsNo,getDate()
  From Inserted
GO

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

CREATE TRIGGER tr_AdjustRights_Update ON AdjustRights 
FOR UPDATE
AS
  Update AdjustRights  set ModifyDate=GetDate()
  From AdjustRights,Inserted
  Where AdjustRights.GoodsNo=Inserted.GoodsNo
	and AdjustRights.Deptno=Inserted.DeptNO

GO

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

CREATE TRIGGER tr_AdjustRights_Delete ON adjustrights 
FOR delete
AS
  Insert DelData (Tablename,key1,key2,Deldate)
  select 'AdjustRights',DeptNo,GoodsNo,GetDate()
  From Inserted

GO

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

CREATE TRIGGER Tr_OrdersRights_Update ON ORDERSRIGHTS 
FOR UPDATE
AS
  Update OrdersRights set modifydate=GetDate()
  From OrdersRights,Inserted
  Where OrdersRights.Deptno=Inserted.DeptNo
  and OrdersRights.GoodsNo=Inserted.GoodsNo
GO

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

CREATE TRIGGER Tr_OrdersRights ON ORDERSRIGHTS 
FOR DELETE 
AS
 Insert DelData ( TableName,key1,Key2,DelDate )
 Select 'ordersrights',DeptNo,GoodsNo,GetDate()
 From Inserted
GO

/****** Object:  Trigger tr_paymode_update    Script Date: 00-4-3 9:30:40 ******/
if exists ( select * from sysobjects where name='tr_Paymode_Update' and type='tr')
	Drop Trigger Tr_Paymode_update
go

CREATE TRIGGER tr_paymode_update
ON paymode
FOR update
AS
  update PayMode set ModifyDate=GetDate() 
  where PayMode in (select PayMode from inserted)


GO

/****** Object:  Trigger tr_paymode_delete    Script Date: 00-4-3 9:30:40 ******/
if exists ( select * from sysobjects where name='tr_PayMode_Delete' and type='tr')
	Drop trigger tr_paymode_delete
go

CREATE TRIGGER tr_paymode_delete
ON paymode
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'PayMode',PayMode,GetDate() from Deleted

GO
/****** Object:  Trigger tr_staff_update    Script Date: 00-4-3 9:30:40 ******/
if exists ( select * from sysobjects where name='tr_staff_Update' and type='tr')
	Drop Trigger tr_staff_update
go

CREATE TRIGGER tr_staff_update
ON staff
FOR UPDATE
AS
  UPDATE Staff SET ModifyDate=GetDate() 
  where Operator in (select Operator from inserted)

GO