I have been trying to create an SQL trigger to update the amount value in the lawyer table every time a new row is added to the billing table. So far my code has not been changing anything.
Yet when I run it nothing changes. I have it set so that the default value in amount is 0, and that is not changing.
CREATE OR REPLACE FUNCTION trigf1()
RETURNS TRIGGER AS $$
BEGIN
-- Update the amount in the billing table
UPDATE billing
SET amount = OLD.amount
+ NEW.hours * (
SELECT hbilling
FROM lawyer
WHERE lname = NEW.lname
)
WHERE bdate = OLD.bdate
AND lname = OLD.lname
AND cid = OLD.cid;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER t1
AFTER INSERT ON billing
FOR EACH ROW
EXECUTE FUNCTION trigf1();
I have tried changed when it is called and changed the actual way amount is calculated. I am pretty new to SQL (a few days in) so I still don’t exactly know what I need to be doing different. Is it possible that I am simply not setting the trigger to run at the correct time?
1