Wiki Home

Triggers And Stored Procedures


Namespace: VFP
Once a Check or Invoice is closed (lCheckClossed or lInvoiceClosed =1) I want to block all changes to the Check or Invoice Entity (record and child records).


Quite a bit of Cut and Paste was used to create the following triggers, which makes me think there is a better way.

(note: I haven't tested, got this far and decided maybe I was going down the wrong road.)


-- Invoice header (all)
create trigger ClosedIsClosedI on dbo.Invoice for Update, Delete as
DECLARE @lClosed bit
Select @lClosed = lInvoiceClosed 
	FROM deleted 
IF (@lClosed = 1)
BEGIN
   RAISERROR ('Invoice closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END

go

-- Pay Check Header (all)
create trigger ClosedIsClosedPC on dbo.PayCheck for Update, Delete as
DECLARE @lClosed bit
Select @lClosed = lCheckClosed 
	FROM deleted 
IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END

go

-- Pay Check Detail, Ins/Update
create trigger ClosedIsClosedPCDiu on dbo.PayCheckDetail for Insert, Update as
DECLARE @lClosed bit

Select @lClosed = PayCheck.lCheckClosed 
	FROM dbo.PayCheck 
	join inserted on PayCheck.kPayCheck_pk = inserted.kPayCheck_fk 

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
go

-- Pay Check Detail, Delete
create trigger ClosedIsClosedPCDd on dbo.PayCheckDetail for Delete as
DECLARE @lClosed bit

Select @lClosed = PayCheck.lCheckClosed 
	FROM dbo.PayCheck 
	join deleted on PayCheck.kPayCheck_pk = deleted.kPayCheck_fk 

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
go


-- BillPay Item,  Ins/Update
create trigger ClosedIsClosedBPIiu on dbo.BillPayItem for Insert, Update as
DECLARE @lClosed bit

Select @lClosed = PayCheck.lCheckClosed 
	FROM dbo.PayCheck 
	join inserted on PayCheck.kPayCheck_pk = inserted.kPayCheck_fk 

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
else
Select @lClosed = Invoice.lInvoiceClosed 
	FROM dbo.Invoice 
	join inserted on Invoice.kInvoice_pk = inserted.kInvoice_fk 

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Invoice closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END

go


-- BillPay Item, delete
create trigger ClosedIsClosedBPId on dbo.BillPayItem for delete as
DECLARE @lClosed bit

Select @lClosed = PayCheck.lCheckClosed 
	FROM dbo.PayCheck 
	join deleted on PayCheck.kPayCheck_pk = deleted.kPayCheck_fk 

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
else
Select @lClosed = Invoice.lInvoiceClosed 
	FROM dbo.Invoice 
	join deleted on Invoice.kInvoice_pk = deleted.kInvoice_fk 

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Invoice closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END

go


-- BillPay Item Detail,  Ins/Update
create trigger ClosedIsClosedBPDiu on dbo.BillPayDetail for Insert, Update as
DECLARE @lClosed bit

Select @lClosed = PayCheck.lCheckClosed 
	FROM dbo.PayCheck 
	join BillPayItem on PayCheck.kPayCheck_pk = BillPayItem.kPayCheck_fk 
	join inserted on BillPayItem.kBillPayItem_pk = inserted.kBillPayItem_fk

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
else
Select @lClosed = Invoice.lInvoiceClosed 
	FROM dbo.Invoice 
	join BillPayItem on Invoice.kInvoice_pk = BillPayItem.kInvoice_fk 
	join inserted on BillPayItem.kBillPayItem_pk = inserted.kBillPayItem_fk

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Invoice closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
go


-- BillPay Item Detail,  delete
create trigger ClosedIsClosedBPDd on dbo.BillPayDetail for delete as
DECLARE @lClosed bit

Select @lClosed = PayCheck.lCheckClosed 
	FROM dbo.PayCheck 
	join BillPayItem on PayCheck.kPayCheck_pk = BillPayItem.kPayCheck_fk 
	join deleted on BillPayItem.kBillPayItem_pk = deleted.kBillPayItem_fk

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Check closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END
else
Select @lClosed = Invoice.lInvoiceClosed 
	FROM dbo.Invoice 
	join BillPayItem on Invoice.kInvoice_pk = BillPayItem.kInvoice_fk 
	join deleted on BillPayItem.kBillPayItem_pk = deleted.kBillPayItem_fk

IF (@lClosed = 1)
BEGIN
   RAISERROR ('Invoice closed. no updates allowed.',16,1)
   ROLLBACK TRANSACTION
END

go


( Topic last updated: 2004.01.12 06:06:31 PM )