Which executes first? Trigger or rule?

Question

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;

Answer 1

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.

Answer 2

@Chris provides an excellent answer for the question in the title.

Your question itself is inconsistent in multiple places, talking about a trigger "after delete", but showing a trigger AFTER INSERT. Rule and trigger are on different tables and your trigger function is needlessly convoluted. A clean way could look like this:

CREATE OR REPLACE FUNCTION funk_wyp()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE zyskiklient z
   SET    ile = z.ile + f.cena      -- no separate query 
   FROM   filmy f
   WHERE  f.id = NEW.co             -- see below!
   AND    z.kto = NEW.kto;

   UPDATE istotnoscklienta
   SET    poziom = poziom(NEW.kto)  -- no subselect
   WHERE  kto = NEW.kto;

   RETURN NEW;  -- ?!?
END
$func$;

Note a subtle difference: my first UPDATE will do nothing if no row for f.id = NEW.co is found - as opposed to your original which will update anyway and set the column to NULL. Typically, you would want the behavior of my version.

But it still wouldn't make sense to RETURN NEW in a trigger AFTER INSERT (or AFTER DELETE). The manual here:

The return value is ignored for row-level triggers fired after an operation, and so they can return NULL.

... and here:

... and DELETE triggers cannot refer to NEW.

Start by reading the manual on UPDATE, PL/pgSQL functions, trigger functions and CREATE TRIGGER.