Prevent value of SQL column from going negative with a trigger?-CodePudding

I've got a table, let's say it's got two columns, person and n_dollars, where n_dollars represents the amount of dollars paid or received, something like

Person	n_dollars
First	100
First	-100
Second	100
Second	200

And so the total amount of money someone has can be found by summing over n_dollars. I want to prevent this value from ever going negative, so if we attempt to insert

Person	n_dollars
First	-1

We receive an error. How can I accomplish this? I'm thinking I would do this with a trigger, something like:

CREATE TRIGGER prevent_negative
  BEFORE INSERT
  ON table
  FOR EACH ROW
BEGIN
  IF NEW.n_dollars   SUM(n_dollars) THEN
    RAISE EXCEPTION
END

CodePudding user response：

The simplest way is to add a CHECK constraint to your table, or include it in the table definition when you create it.

ALTER TABLE tbl_name 
ADD CONSTRAINT chk_dollars_positive 
CHECK ( n_dollars>= 0);

CodePudding user response：

I know it's not postgresql, but if you really want a trigger to create a log or email the error data, I'd do something similar in SQL Server

CREATE OR ALTER TRIGGER t_insert
    ON dbo.[table]
INSTEAD OF INSERT  
AS
BEGIN
    IF(ROWCOUNT_BIG() = 0)
        RETURN;

    declare @persona varchar(100)
    select @persona = persona from inserted

    DECLARE @money decimal
    select @money = [money] from inserted 


    IF((SELECT SUM([MONEY]) FROM [table] WHERE PERSONA = @PERSONA)   @MONEY >= 0)
    BEGIN
        INSERT INTO [TABLE] ([PERSONA], [MONEY])
        SELECT [PERSONA], [MONEY] FROM inserted
    END
    ELSE 
    BEGIN
        PRINT 'the balance cannot be negative'
    END


END