rockodile rockodile - 2 years ago 69
SQL Question

my sql trigger compiles but doesnt fire when necessary

im trying to make a trigger for my tables. this trigger is supposed to fire when my timesheet has been approved which will calculate the payment due that is standard pay, overtime pay and pension pay. pension is only due when the standard pay is over 250. this should do this for the previous week and it should update my payroll table working out what the next ID would be and insert a new record into payroll when theres a new record inserted into my timesheet table.

after hours of trying to get it to work, i finally got to a point where i didnt know where my problem was because i no longer get errors.
i figured it might be with my joins but after some alterations it still doesn't work properly.

would appreciate any help. thanks!

Answer Source

null is not a value - it's the lack thereof. As such, the result of using it with any operator that expects a value (such as != in your case) is "unknown", which is not "true", so the condition won't be fired.

To make a long story short - instead of using the != operator, you should be using the is not operator:

WHEN (new.timesheet_approved IS NOT NULL)
-- Here ---------------------^
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download