/--MySql --PostSQL Using the tables created by the attached SQL code, we will create two triggers on the TABLE tblBranchTransactions: The first trigger executes BEFORE INSERT and checks that the accntBalance in tblAccntMaster + inserted transaction amount (transAmt) will be >= 0. Hint: Since accntBalance and transAmt are MONEY, you will have to CAST the integer zero to MONEY! IF accntBalance+transAmt < CAST(0 TO MONEY) That works, but it's tedious. PostgreSQL gives us a shortcut version of the cast: IF accntBalance+transAmt < 0::MONEY Same thing, only less typing. If the result will be less than zero, RETURN NULL (abort). -- -------------------------- The second trigger will execute AFTER INSERT ON tblBranchTransactions and will update the balance in tblAccntMaster to reflect the current balance. I.e.: It will execute an UPDATE tblAccntMaster such that accntBalance = accntBalance+transAmt... where transAmt may be negative. (This is OK, so long as accntBalance isn't negative.) Note, these are both simple triggers. You will not use dblink or any two-step processing such as we did in the logging examples. Each part consists of a trigger coupled with a trigger function. Two parts means two of each. Both run as SECURITY INVOKER, which is the default. assignment using one file. One little caveat for PostgreSQL triggers: if you alter the underlying tables (i.e. add a constraint or modify a field), the triggers and their associated functions must be recompiled, otherwise, they just stop working! */ -- database: "accnts" DROP TABLE IF EXISTS tblBranchTransactions; DROP TABLE IF EXISTS tblAccntMaster; CREATE TABLE tblAccntMaster ( accntID CHAR(16), accntBalance MONEY DEFAULT 0, CONSTRAINT accntPK PRIMARY KEY(accntID) ); CREATE TABLE tblBranchTransactions ( transID INTEGER, accntID CHAR(16), transDate DATE, transAmt MONEY, CONSTRAINT transPK PRIMARY KEY(transID), CONSTRAINT masterFK FOREIGN KEY(accntID) REFERENCES tblAccntMaster(accntID) ); -- ---------------------------------------- DROP SEQUENCE IF EXISTS seqTransID; CREATE SEQUENCE seqTransID; -- Gives an easy way to assign the transID if you want it. -------------------------------------------