Re: Controling Rule's Firing Order - Mailing list pgsql-general

Hello! Tom,

Thank you very much for the patience! Probably you are my last hope.

> Foreign key triggers will fire in alphabetical order along with other triggers.
> This means they're firing based on a textual sort of the OIDs assigned to the
> triggers, which will often but not always correspond to creation order.

I don't fully understand the meaning of the term "firing in alphabetic
order" you mentioned earlier and "they're firing based on a textual sort
of the OIDs assigned to the triggers". OIDs are integers. Why terms
"textual" and "alphabetic" are used?  Did you actually referring them to
the _names_ of triggers, rules, and FK triggers, instead of their OIDs?

Hopefully the answer is not "their oids". Triggers/FK triggers/rules are
frequently dropped and recreated during database development stage.
Please imagine I have rule1, FK trigger2, trigger3, and trigger4 to be
fired in this listed order. If I change the logic of rule1, then I will
also have to remember to drop FK trigger2, trigger3, and trigger4 and
recreate them after rule1 is dropped and recreated. Seeing this, I feel
using OIDs as the control for firing order makes database design work
very complicate and the design result is prone to errors.

Now back to the topic of your concern about the value of controling FK
trigger's firing order. I have prepared this example trying to prove
that they are worthy being added to the already very powerful
postgresql. I want the system to work in this way:

- When exchange rate in SaleMaster is changed or when price in
SaleDetail is inserted/updated/deleted, VoucherDetail reflects the
change immediately.
- VoucherMaster is not created until SaleDetail is inserted.
- VocherMaster is also deleted when SaleMaster is deleted.

Problems arise if I have no control over the firing order of FK trigger
as the following 2 scenarios.

Scenario 1: When SaleNumber in SaleMaster is updated.
If trigger SaleMasterU is fired before FK trigger fkSale, then statement

FOR rec IN SELECT * FROM SaleDetail WHERE SaleNumber=varSaleNo LOOP

in UpdateVoucher(), called by trigger SaleMusterU, will do nothing. This
is because column SaleNumber in SaleDetail now still keeps old value
while varSaleNo contains new one. The net result is that the data is
incorrect.

Scenario 2: When SaleMaster is deleted.
VoucherMaster will be deleted by trigger SaleMasterD. However, if
trigger SaleMasterD is fired before FK trigger fkSale, VoucherMaster
will be re-inserted by trigger SaleDetailD which is an orphan master
row.

Regards,

CN
==============
CREATE TABLE SaleMaster(
SaleNumber INTEGER PRIMARY KEY,
ExchangeRate NUMERIC
);

CREATE TABLE SaleDetail(
CONSTRAINT fkSale FOREIGN KEY (SaleNumber) REFERENCES SaleMaster
(SaleNumber) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (SaleNumber,ItemSold),
SaleNumber INTEGER,
ItemSold TEXT,
price NUMERIC
);

CREATE TABLE VoucherMaster(
VoucherNumber SERIAL PRIMARY KEY,
SaleNumber INTEGER
);

CREATE TABLE VoucherDetail(
CONSTRAINT fkVoucher FOREIGN KEY (VoucherNumber) REFERENCES
VoucherMaster (VoucherNumber) ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (VoucherNumber,item),
VoucherNumber SERIAL PRIMARY KEY,
item TEXT,
amount NUMERIC
);

CREATE FUNCTION UpdateVoucher(TEXT) RETURNS BOOLEAN AS '
DECLARE
  varSaleNo ALIAS FOR $1;
  varExchangeRate NUMERIC;
  varVoucherNo TEXT;
  varItem TEXT;
  rec RECORD;
  amount NUMERIC;
BEGIN
  SELECT ExchangeRate INTO varExchangeRate FROM SaleMaster WHERE
SaleNumber=varSaleNo;

  SELECT VoucherNo INTO varVoucherNo FROM VoucherMaster WHERE
SaleNo=varSaleNo;
  IF NOT FOUND THEN
    INSERT INTO VoucherMaster(SaleNo) VALUES (varSaleNo);
  END IF;

  --Delete all VoucherDetail and regenerate.
  DELETE FROM VoucherDetail WHERE VoucherNo=varVoucherNo;

  FOR rec IN SELECT * FROM SaleDetail WHERE SaleNumber=varSaleNo LOOP
    --The logic for the real function is more complicate than this
example.
    --That is this function exists for.
    varItem:=rec.ItemSold;
    amount:=rec.price*varExchangeRate;
    INSERT INTO VoucherDetail VALUES (VoucherNumber,varItem,amount);
  END LOOP;

  RETURN TRUE;
END;' LANGUAGE 'plpgsql';
-----------------------
CREATE FUNCTION FuncSaleMasterU() RETURNS OPAQUE AS '
BEGIN
  IF NEW.SaleNumber <> OLD.SaleNumber THEN
    UPDATE VoucherMaster SET SaleNo=NEW.SaleNumber WHERE
SaleNo=OLD.SaleNumber;
  END IF;

  IF NEW.ExchangeRate <> OLD.ExchangeRate THEN
    PERFORM UpdateVoucher(NEW.SaleNumber);
  END IF;

  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleMasterU AFTER UPDATE ON SaleMaster FOR EACH ROW
EXECUTE PROCEDURE FuncSaleMasterU();
-----------------------
CREATE FUNCTION FuncSaleMasterD() RETURNS OPAQUE AS '
BEGIN
  --If sale master is gone, delete the corresponding voucher master,
too.
  DELETE FROM VoucherMaster WHERE SaleNo=OLD.SaleNumber;
  RETURN OLD;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleMasterD BEFORE DELETE ON SaleMaster FOR EACH ROW
EXECUTE PROCEDURE FuncSaleMasterD();
-----------------------
CREATE FUNCTION FuncSaleDetailI() RETURNS OPAQUE AS '
BEGIN
  PERFORM UpdateVoucher(NEW.SaleNumber);
  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleDetailI AFTER INSERT ON SaleDetail FOR EACH ROW
EXECUTE PROCEDURE FuncSaleDetailI();
-----------------------
CREATE FUNCTION FuncSaleDetailU() RETURNS OPAQUE AS '
BEGIN
  IF NEW.ItemSold <> OLD.ItemSold THEN
    UPDATE VoucherDetail SET item=NEW.ItemSold WHERE
VoucherNumber=(SELECT VoucherNumber FROM VoucherMaster WHERE
SaleNo=NEW.SaleNumber) AND item=OLD.ItemSold;
  END IF;

  IF NEW.price <> OLD.price THEN
    PERFORM UpdateVoucher(NEW.SaleNumber);
  END IF;

  RETURN NEW;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleDetailU AFTER UPDATE ON SaleDetail FOR EACH ROW
EXECUTE PROCEDURE FuncSaleDetailU();
--------------------------
CREATE FUNCTION FuncSaleDetailD() RETURNS OPAQUE AS '
BEGIN
  PERFORM UpdateVoucher(OLD.SaleNumber);
  RETURN OLD;
END;' LANGUAGE 'plpgsql';

CREATE TRIGGER SaleDetailD AFTER DELETE ON SaleDetail FOR EACH ROW
EXECUTE PROCEDURE FuncSaleDetailD();