sypostr.sql

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

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

CREATE TRIGGER tr_staff_delete
ON staff
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'staff',Operator,GetDate() from Deleted

GO


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

CREATE TRIGGER tr_team_update
ON team
FOR UPDATE
AS
  UPDATE Team SET ModifyDate=GetDate() 
  where GroupNo in (select GroupNo from inserted)
GO

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

CREATE TRIGGER tr_team_delete
ON team
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'team',GroupNo,GetDate() from Deleted

GO

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

CREATE TRIGGER Tr_topandbottom_Delete ON TOPANDBOTTOM 
FOR Delete
AS
   Insert DelData(tablename,key1,key2)
   Select 'topandbottom',deptno,goodsno from inserted
GO

if exists ( select * from sysobjects where name='tr_topandbottom_update' and type='tr')
	Drop trigger tr_topandbottom_Update
go

CREATE TRIGGER Tr_topandbottom_Update ON TOPANDBOTTOM 
FOR Update
AS
  update TopAndBottom set ModifyDate=GetDate()
  From TopAndBottom,Inserted
  Where TopAndbottom.DeptNo=Inserted.Deptno and
	TopAndbottom.GoodsNo=Inserted.GoodsNo
GO

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

CREATE TRIGGER Tr_Transdis ON TRANSDIS 
FOR UPDATE 
AS
  Update Transdis set Modifydate=getdate()
GO

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

CREATE TRIGGER Tr_UserDefField ON USERDEFFIELD 
FOR UPDATE
AS
  Update UserDefField set Modifydate=Getdate(),IsUsing = '1'
  From UserDefField,Inserted
  Where UserDefField.TableName=Inserted.TableName
  and UserDefField.FieldName=Inserted.FieldName
GO

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

CREATE TRIGGER tr_vendor_update
ON vendor
FOR UPDATE
AS
  UPDATE Vendor SET ModifyDate=GetDate() 
  where VendorNo in (select VendorNo from inserted)

GO

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

CREATE TRIGGER tr_vendor_delete
ON vendor
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'vendor',VendorNo,GetDate() from Deleted

GO

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

CREATE TRIGGER tr_vendorprice_update
ON vendorprice
FOR UPDATE
AS
  UPDATE VendorPrice SET ModifyDate=GetDate() 
  FROM VendorPrice a,Inserted b
  where a.VendorNo=b.VendorNo and a.GoodsNo=b.GoodsNo
GO

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

CREATE TRIGGER tr_vendorprice_delete
ON vendorprice
FOR DELETE
AS
  insert DelData(TableName,key1,key2,DelDate) 
  select 'VendorPrice',VendorNo,GoodsNo,GetDate() from Deleted

GO

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

CREATE TRIGGER tr_worktype_update
ON worktype
FOR UPDATE
AS
  UPDATE WorkType SET ModifyDate=GetDate() 
  where WorkType in (select WorkType from inserted)

GO

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

CREATE TRIGGER tr_worktype_delete
ON worktype
FOR DELETE
AS
  insert DelData(TableName,key1,DelDate) 
  select 'worktype',worktype,GetDate() from Deleted

GO

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

Create Trigger tr_ReachRights_Delete on ReachRights
for Delete
as
   Insert DelData(tablename,key1,key2)
  Select 'reachrights',deptno,goodsno 
  from Deleted

go

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

Create Trigger tr_ReachRights_Update on ReachRights
for Update
as
   Update ReachRights Set Modifydate=getdate()
   From ReachRights,Inserted
   Where ReachRights.DeptNo=Inserted.DeptNo
   and ReachRights.GoodsNo=Inserted.GoodsNo

go

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

Create Trigger Tr_GroupRights_Delete On GroupRights
for Delete
as
  insert delData (tablename,key1,key2)
 Select 'grouprights',operator,groupno
 from Deleted

go

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

Create Trigger Tr_GroupRights_Update on GroupRights
for Update
as
  Update GroupRights set ModifyDate=getdate()
  from GroupRights,Inserted
  Where GroupRights.Operator=Inserted.Operator
  and Grouprights.GroupNo=Inserted.GroupNo

go

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

Create Trigger Tr_Category_Delete on Category
for  Delete
as
  Insert DelData ( TableName,key1)
  Select 'category',categoryno from Deleted

go

/*---------------------------------------------------------------------------------*/
/****** Object:  Trigger TR_orders_Insert    Script Date: 00-4-3 14:47:31 ******/

if (select count(*) from sysobjects where type='tr' and name='TR_orders_Insert')>0
	drop trigger TR_orders_Insert
go


CREATE TRIGGER TR_orders_Insert ON orders
FOR INSERT
AS
declare @deptno char(4)
declare @orderno char(10)
declare @localflag char(1)

select @orderno=orderno,@deptno=Inputdept from inserted
select @localflag=localflag from dept where deptno=@deptno

if @localflag='1'
	Execute Up_UpdateReceiptNo 'orders',@deptno,@OrderNo
go
print 'TR_orders_Insert is created already.'                                                                                                                                                       
go


/*-------------------------------------------------------------------------------------*/
/****** Object:  Trigger TR_subimport_Insert    Script Date: 00-4-3 14:47:31 ******/
if (select count(*) from sysobjects where type='tr' and name='TR_subimport_Insert')>0
	drop trigger TR_subimport_Insert
go


CREATE TRIGGER TR_subimport_Insert ON subimport 
FOR INSERT
AS

declare @deptno char(4)
declare @importno char(10)

select @importno=importno,@deptno=Inputdept from inserted

Execute Up_UpdateReceiptNo 'subimport',@deptno,@Importno
go
print 'TR_subimport_Insert is created already.'                                                                                                                            
go                                                                                                                        


/*-------------------------------------------------------------------------------*/
/****** Object:  Trigger TR_Req_Insert    Script Date: 00-4-3 14:47:31 ******/
if (select count(*) from sysobjects where type='tr' and name='TR_req_Insert')>0
	drop trigger TR_req_Insert
go


CREATE TRIGGER TR_Req_Insert ON Req 
FOR INSERT
AS

declare @deptno Tdept
declare @receiptno Treceipt
declare @localflag char(1)

select @receiptno=receiptno,@deptno=Inputdept from inserted
select @localflag=localflag from dept where deptno=@deptno

if @localflag='1'
 Execute Up_updateReceiptno 'req',@deptno,@ReceiptNo
go

print 'TR_req_Insert is created already.'                                                                                                                                                             
go