sql - Trigger not disabling

Question

create or replace NONEDITIONABLE TRIGGER azuriranje
AFTER UPDATE OF napomena
ON otpremnica
FOR EACH ROW
DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER zabranjeno DISABLE';
UPDATE stavkaotpremnice
SET napomena =:NEW.napomena
WHERE brojotpremnice =:NEW.brojotpremnice;
EXECUTE IMMEDIATE 'ALTER TRIGGER zabranjeno ENABLE';
END;

Does anyone knows why the trigger "zabranjeno" is not disabling before the update?

Answer 1

This is at least 3rd question you posted, related to the same problem. I answered one of them (this one), so - what's the point in asking thet same question all over again?

Anyway: reading what you posted (as questions or comments), I'd say that you're doing it completely wrong. Here's why:

• you have two tables: otpremnica and stavkaotpremnice
• you don't want to allow users to update napomena column in stavkaotpremnice and, therefore, created the zabranjeno trigger on that table
• at the same time, trigger azuriranje on table otpremnica should
  • disable trigger zabranjeno
  • set stavkaotpremnice.napomena = otpremnica.napomena for that brojotpremnice, for all rows in stavkaotpremnice
  • enable trigger zabranjeno

The last bullet ("at the same time ...") shows why this is wrong. Your data model violates the 3rd normal form; there's no point & no sense in keeping the same napomena value in both master (otpremnica) and detail (stavkaotpremnice) tables.

If you want to show (display) napomena with all rows in stavkaotpremnice, then join those tables, e.g.

select o.brojotpremnice,
       s.brojstavke,
       s.nazivstavke,
       o.napomena              --> here's your NAPOMENA
from otpremnica o join stavkaotpremnice s on s.brojotpremnice = o.brojotpremnice
where ...

Finally, what's the point of having the napomena column in stavkaotpremnice if you won't allow users to enter any note they find appropriate?

---

Therefore, to cut a long story short, to solve your nightmare, modify data model:

• remove napomena from stavkaotpremnice
• drop both zabranjeno and azuriranje triggers
• fetch napomena from otpremnica table, whenever needed
  • alternatively, create a view using the query I posted above