Postgresql – Which executes first? Trigger or rule

database-designpostgresqltrigger

Using PostgreSQL.

Had a trigger which does something after delete and made a rule which stopped delete from happening if something will be true.

My question is: will the rule prevent the trigger from occurring as well?

The trigger definition:

CREATE TRIGGER wypo AFTER INSERT ON "Wyp" FOR EACH ROW EXECUTE PROCEDURE funk_wyp();

The trigger function

CREATE OR REPLACE FUNCTION funk_wyp() RETURNS TRIGGER AS '
DECLARE
  dodatek real := (SELECT f.cena FROM filmy f WHERE f.ID = NEW.co);
BEGIN
    UPDATE "zyskiklient" 
       SET "ile" = ile + dodatek 
    WHERE kto = NEW.kto; 
    UPDATE "istotnoscklienta" 
       SET "poziom" = (SELECT poziom(NEW."kto")) 
    WHERE "kto" = NEW.kto;
RETURN NEW;
END; 
' LANGUAGE 'plpgsql';

The rule:

CREATE OR REPLACE RULE zasada_delete_konto 
  AS ON DELETE TO "konto" 
WHERE konto_zwrocone(OLD."ID") = FALSE 
DO INSTEAD NOTHING;

Best Answer

You can consider a rule as transforming the command being executed, whereas a trigger is altering the data itself. (Note that this is a simplification! Spend some time reading the documentation for CREATE TRIGGER and CREATE RULE rather than trusting some random internet guy.)

So you can define a rule that is invoked when PostgreSQL sees a certain command, and transforms that command into something that would not invoke the trigger.

Take for instance a rule defined as such:

CREATE OR REPLACE RULE dont_insert AS
ON INSERT TO xyz.items
DO INSTEAD NOTHING;

And a trigger defined as such:

CREATE TRIGGER insert_with_func BEFORE INSERT
ON xyz.items FOR EACH ROW
EXECUTE PROCEDURE xyz.row_update_func();

Attempting to do an insert will not invoke row_update_func(), even if the trigger is defined as BEFORE INSERT, because the rule is modifying the command itself; after the command is re-evaluated, it's no longer doing anything, and the trigger no longer applies.

Obviously your rule does not need to be defined with INSTEAD OF, and in that case, your rule and your trigger could both apply.

With specific regard to your code, it's hard to say without knowing the definition of konto_zwrocone, but it doesn't look like it since your trigger and rule are defined for different operations on different tables.

Related Question