IS NULL in a MySQL tirgger...how do I do it?

[Mike Reed]

I have this trigger set in a MySQL table.
It will, of course, not work when the value in AMOUNTPAID is a null value.

How can I redo this trigger to accommodate a null value in the amountpaid field? I think I need to use an if statement in the trigger, but I cannot figure out the correct syntax.
Anyone have any thoughts?

CREATE DEFINER=`root`@`localhost` TRIGGER `dbname`.`acct_upd` AFTER INSERT ON dbname.accounts_paid FOR EACH ROW
BEGIN
UPDATE accounts
SET accounts.balance = accounts.balance - new.amount, accounts.amountpaid = accounts.amountpaid + new.amount
WHERE accounts.invno = new.invno;
END;

Thanks,
mike

[Jim Coltz]

Re: IS NULL in a MySQL tirgger...how do I do it?

You can use the IFNULL() function.

SET accounts.balance = ifnull(accounts.balance,0) - ifnull(new.amount,0), accounts.amountpaid = ifnull(accounts.amountpaid,0) + ifnull(new.amount,0)

[TheSmitchell]

Re: IS NULL in a MySQL tirgger...how do I do it?

Just a thought: Maybe you should default your values to 0 instead of NULL...

IFNULL will work, but the community largely favors COALESCE(new.amount, 0), which does pretty much the same thing. COALESCE returns the first non-NULL parameter, or NULL if they're all NULL.

IFNULL is a MySQL specific function. MSSQL does not have an IFNULL. But it does have a COALESCE. COALESCE is in the the SQL standard, so a lot of SQL DBs support it.

Alternatively, you can just write IF(new.amount IS NULL, 0, new.amount). A bit wordier, but just as portable.

[GGandhi]

Re: IS NULL in a MySQL tirgger...how do I do it?

just another thought:
why there is a need to keep the balance in the table. can't they be calculated where needed. so there will be amount credited and debited only. wherever you need the calculation can be performed and reported.
another thought:
in finance industry there is no NULL. there are only positive numbers, either they are on the left side or on the right side ( credit or debit).

[Mike Reed]

Re: IS NULL in a MySQL tirgger...how do I do it?

Hi,
Thanks for your replies.
I could not get the ISNULL() function to work. I tried all kinds formatting including what you had Jim, but the trigger would fail as having an invalid syntax. So I gave up on that.
I changed the default on the table so that the field would be 0, not null when a new row was added, updated all null values to 0, so Sarah that obviously works fine. Also tried the COALESCE function, but couldn't that to work either. The zero takes care of the problem.
And gandhi, the table is basically the accounts receivable table, so when we run statements, the report only has to look for a balance <> 0 and it will print. Saves the time to search through all the payment rows.

Thanks Again,

Mike