USE [simpleAccounting]
GO
/****** Object:  Table [dbo].[invoice]    Script Date: 07/04/2009 10:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[invoice](
	[InvoiceID] [int] IDENTITY(1,1) NOT NULL,
	[Amount] [money] NOT NULL CONSTRAINT [DF_invoice_Amount]  DEFAULT ((0.00)),
 CONSTRAINT [PK_invoice] PRIMARY KEY CLUSTERED 
(
	[InvoiceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[payment]    Script Date: 07/04/2009 10:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[payment](
	[paymentID] [int] IDENTITY(1,1) NOT NULL,
	[amount] [money] NOT NULL CONSTRAINT [DF_payment_amount]  DEFAULT ((0.00)),
 CONSTRAINT [PK_payment] PRIMARY KEY CLUSTERED 
(
	[paymentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[posting]    Script Date: 07/04/2009 10:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[posting](
	[postingID] [int] IDENTITY(1,1) NOT NULL,
	[invoiceID] [int] NULL,
	[paymentID] [int] NULL,
	[amount] [money] NULL,
 CONSTRAINT [PK_posting] PRIMARY KEY CLUSTERED 
(
	[postingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Trigger [check_totals_update]    Script Date: 07/04/2009 10:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[check_totals_update] ON [dbo].[posting] after UPDATE
AS 
DECLARE @lc_insert_amount as char(20)
DECLARE @lc_delete_amount as char(20)
DECLARE @lc_sum_posted as char(20)
DECLARE @lc_invoice_total as char(20)
DECLARE @lc_message as char(254)

BEGIN

IF EXISTS(SELECT 1 FROM inserted i WHERE 
	((SELECT SUM(posting.amount) FROM posting posting WHERE posting.invoiceID=i.InvoiceID)>
	(SELECT Invoice.Amount FROM Invoice Invoice WHERE invoice.InvoiceID=i.InvoiceID)
)
	)
	BEGIN
		select @lc_insert_amount=CAST(amount as varchar(20)) from inserted
		select @lc_delete_amount=CAST(amount as varchar(20)) from deleted
		select @lc_sum_posted=CAST(SUM(posting.amount) as varchar(20)) FROM inserted i RIGHT OUTER JOIN posting posting ON posting.invoiceID=i.InvoiceID
		select @lc_invoice_total=CAST(Invoice.amount as varchar(20)) FROM inserted i LEFT OUTER JOIN Invoice Invoice ON Invoice.invoiceID=i.InvoiceID

		select @lc_message='Attempt to UPDATE row when Posting Amount > Invoice Amount - Inserted: '+@lc_insert_amount+
		' - Deleted: '+@lc_delete_amount+
		' - sum of posted amount: '+@lc_sum_posted+
		' - Invoice Total: '+@lc_invoice_total
		
		ROLLBACK TRANSACTION
		RAISERROR (@lc_message, 16, 1)
	END
end
GO
/****** Object:  Trigger [check_total_insert]    Script Date: 07/04/2009 10:47:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================
create TRIGGER [dbo].[check_total_insert] on [dbo].[posting] for insert 
AS

IF EXISTS(SELECT 1 FROM inserted i WHERE 
	(i.Amount + (SELECT SUM(amount) from posting where posting.invoiceID=i.InvoiceID) > 
	(SELECT Amount from Invoice WHERE invoice.InvoiceID=i.InvoiceID))
	)
	BEGIN
		ROLLBACK TRANSACTION
		RAISERROR ('Attempt to insert row when Posting Amount > Invoice Amount', 16, 1)
	END
GO
/****** Object:  ForeignKey [FK_posting_invoice]    Script Date: 07/04/2009 10:47:27 ******/
ALTER TABLE [dbo].[posting]  WITH CHECK ADD  CONSTRAINT [FK_posting_invoice] FOREIGN KEY([invoiceID])
REFERENCES [dbo].[invoice] ([InvoiceID])
GO
ALTER TABLE [dbo].[posting] CHECK CONSTRAINT [FK_posting_invoice]
GO
/****** Object:  ForeignKey [FK_posting_payment]    Script Date: 07/04/2009 10:47:27 ******/
ALTER TABLE [dbo].[posting]  WITH CHECK ADD  CONSTRAINT [FK_posting_payment] FOREIGN KEY([paymentID])
REFERENCES [dbo].[payment] ([paymentID])
GO
ALTER TABLE [dbo].[posting] CHECK CONSTRAINT [FK_posting_payment]
GO

quote:

---

Originally posted by webfred

You are doing ROLLBACK TRANSACTION for some conditions.
You want it to work for more than one record.
If there are 10 records and only one is *bad* then a ROLLBACK for all?

It is not what you want to hear (read), but I would prefer not to do this logic in Database. I would like to do it front end app.

Fred

---

No, you're never too old to Yak'n'Roll if you're too young to die.

---

ALTER TRIGGER [dbo].[Update_for_each_record]   
   ON  [dbo].[posting]
  
   AFTER  UPDATE    
AS    
BEGIN  
    DECLARE @InvoiceNumber  int;   
	DECLARE @posted_amount float;
	DECLARE @invoice_total float;

    DECLARE @ICURSOR CURSOR;   
    SET @ICURSOR =CURSOR FOR SELECT InvoiceID FROM deleted;    
    
	OPEN @ICURSOR   
    FETCH NEXT FROM @ICURSOR INTO @InvoiceNumber
    WHILE (@@FETCH_STATUS = 0)    
    BEGIN  
           --do anything you want to do for each row  
			SELECT @posted_amount=SUM(posting.Amount) FROM posting 
				WHERE InvoiceID=@InvoiceNumber
			SELECT @invoice_total=Invoice.amount FROM invoice
				WHERE InvoiceID=@InvoiceNumber
			IF @posted_amount>@invoice_total
				begin 
					rollback transaction
					raiserror('Posted amount more than Invoice amount',16,1)
				end

			-- next record
           FETCH NEXT FROM @ICURSOR INTO @InvoiceNumber;   
        END  
    CLOSE @ICURSOR   
    DEALLOCATE @ICURSOR;   
  
END  

ALTER TRIGGER [dbo].[Update_for_each_record]   
   ON  [dbo].[posting]
  
   INSTEAD OF UPDATE    
AS    
BEGIN  
    DECLARE @Errored varchar(8000)
UPDATE pst
SET pst.Amount=i.Amount
FROM posting pst
JOIN INSERTED i
ON i.PK=pst.PK
JOIN (SELECT InvoiceID,SUM(posting.Amount) AS Total_Post_Amount
FROM posting 
GROUP BY InvoiceID)p
ON p.InvoiceID=i.InvoiceID
JOIN invoice inv
ON i.InvoiceID=inv.InvoiceID
WHERE p.Total_Post_Amount < inv.amount

SELECT @Errored=COALESCE(@Errored+ ';','') + CAST (pst.PK AS varchar(10)) FROM posting pst
JOIN INSERTED i
ON i.PK=pst.PK
JOIN (SELECT InvoiceID,SUM(posting.Amount) AS Total_Post_Amount
FROM posting 
GROUP BY InvoiceID)p
ON p.InvoiceID=i.InvoiceID
JOIN invoice inv
ON i.InvoiceID=inv.InvoiceID
WHERE p.Total_Post_Amount > inv.amount
					
IF @Errored>''
raiserror('Posted amount more than Invoice amount for followed posting %s',16,1,@Errored)
			  
END

quote:

---

Originally posted by visakh16

this will update correct ones and raiserror only for wrong ones

ALTER TRIGGER [dbo].[Update_for_each_record]   
   ON  [dbo].[posting]
  
   INSTEAD OF UPDATE    
AS    
BEGIN  
    DECLARE @Errored varchar(8000)
UPDATE pst
SET pst.Amount=i.Amount
FROM posting pst
JOIN INSERTED i
ON i.PK=pst.PK
JOIN (SELECT InvoiceID,SUM(posting.Amount) AS Total_Post_Amount
FROM posting 
GROUP BY InvoiceID)p
ON p.InvoiceID=i.InvoiceID
JOIN invoice inv
ON i.InvoiceID=inv.InvoiceID
WHERE p.Total_Post_Amount < inv.amount

SELECT @Errored=COALESCE(@Errored+ ';','') + CAST (pst.PK AS varchar(10)) FROM posting pst
JOIN INSERTED i
ON i.PK=pst.PK
JOIN (SELECT InvoiceID,SUM(posting.Amount) AS Total_Post_Amount
FROM posting 
GROUP BY InvoiceID)p
ON p.InvoiceID=i.InvoiceID
JOIN invoice inv
ON i.InvoiceID=inv.InvoiceID
WHERE p.Total_Post_Amount > inv.amount
					
IF @Errored>''
raiserror('Posted amount more than Invoice amount for followed posting %s',16,1,@Errored)
			  
END

---